Can’t determine relationships between the fields: bidirectional

“Can’t determine relationships” is one of the most basic errors in Power BI. Most of the time this error means that a visual has two columns from different tables with no relationship between them.

But, it can also happen when using bi-directional filtering on relationships. The tables are related, but Power BI can’t figure out how to manage the cross-filtering. This error has been spotted before, and the best example in the Power BI Community is this one: https://community.powerbi.com/t5/Desktop/Error-Can-t-determine-relationship-between-fields/m-p/908488.

Using the same data, I investigated some more and found a workaround. Get the Power BI template file from my repo.

The Mystery of Cross-Filtering Relationships

In the model shown below, Date table filters Salesperson and product tables, with cross-filtering direction set to both. Salesperson filters Date, which filters Product. And Product filters Date, which filters Salesperson.

If you put Salesperson[Salesperson] column and Product[Products] column on the same table, you get the error. There is a connection between Salesperson and Product through the Date table, but Power BI can’t figure out how to apply the cross-filtering.

I have four visuals in the image below: the first image is that of the error described above. The next two show working tables from the same dataset. In one, I have Date, First Salesperson, Products; in the next, I have Date, Salesperson, First Products. These table shows that the data is connected. The last table below shows a different model, with a many-to-many relationship, which cross-filters both ways between Salesperson and Product. If the two tables are connected directly, it works, but it doesn’t work with Date table between.

Below, I show a couple of solutions.

The first table shows measures with values in a list. Date and Salesperson are columns from their tables. The third column is a Quick Measure, which lists the values of Products columns. On 1/1/2019, Jack sold Apple, Banana, Orange.

The second table visual shows a workaround. Put Date, Salesperson, and Products from their respective tables on a table visual and you will get the error. Add a value (an aggregation from any of the tables and the error goes away. Below, I put Products on the table and from the field list, I changed it to a Count of Products, an implicit measure. It works the same whether it comes from Date, Salesperson, or Products table.

The matrix visual below shows why this workaround is successful. Matrix visuals clearly show which fields are coordinates for the calculations and which ones are values: coordinates on the left of the blue line, values on the right, just like a pivot table. Remove the value from the matrix and all that’s left are coordinates— and the same error. The value enables Power BI to resolve the coordinates.

Table visuals look simple, but they actually have coordinates and values just like pivot tables and matrixes. I expect to write more about this soon.

Postscript: adding a value to the table is pretty ugly. One way to hide it is to turn off word wrap for column headers, rename the field in the visual to a space character, and manually resize the column to hide it.