cancel
Showing results for 
Search instead for 
Did you mean: 

Single cross filter direction issue: behaving like a right join instead of an inner join

This is a bug found in the 2019 September and 2019 October versions of Power BI Desktop and the same behavior was observed in the Service:


Despite the fact that the direction of the cross filter is set to single (as designed, from the one-side of the relationship to the many-side), the filtering is not behaving as an inner join like it used to, but the filtering is happening as a right join instead (i.e., all rows from the many-side of the relationship and only those rows that match from the one-side are returned). This happens even after changing the order of the tables while setting up the relationship which, as expected, shouldn't matter or even after changing the order of the fields in the visual.


The workaround is to set the filter on all pages to not include blank values from the table on the one-side of the relationship (left side of the join). Please note that these blank values are NOT in the table. They appear in the filter as an option once the relationship is created (right-join behavior).

Status: Accepted
Comments
Community Support

Hi @marcoselias

 

I have reported this issue internally: CRI 156706746. Will update here once I get any information. 

 

Best Regards,
Qiuyun Yu

Community Support
Status changed to: Accepted
 
Community Support

Hi @marcoselias,  

 

The sample data used for reporting the issue is below: 

 

q2.PNG

 

And I got below information internally:

 

This is by design. The table isn't really behaving like a right join. It depends very much on whether something was added as a join predicate or not and whether there are foreign key violations.
In this particular scenario there is a foreign key violation because Table2[Category] has a value that does not exist in Table1[Category] ("d").
When such a foreign key violation exists, the column that has it will end having an "unknown blank value" in the visual. This basically means - the AS engine figured out some data in the foreign table doesn't match the data in the primary table and it adds an unknown value to account for that.

Typically when you build a visual with a measure (SUM of a field, or a custom DAX measure) that measure acts as a constraint of the grouping fields in the visual (join predicate). You end up seeing combinations of fields that result in non blank values for the measure. This allow more complex scenarios like fields from multiple tables that are not directly related to work in visuals.

When a measure is not added, we attempt to figure out a constraint/join predicate such that the values produced are relevant in the database. The way we do that is we examine the relationships between the fields and figure out what are the tables that tie them all together. In this particular case, since there is 1 - M from Table1 to Table2, the table that ties both of them together is Table2 (since it is on the many side).

Due to that we will add a constraint that the combination of Category - Region should have data in Table2 (at least 1 row). We take all combinations of Category - Region and attempt to see if there is data for them in Table2. All the combinations of the values from Table1[Category] with Table2[Region] have corresponding rows in Table. Then we figure out there is an additional value for Category in Table2 that does not exist in Table1 which we end up injecting as an unknown Category value. We match that to its value of Region from Table2. Essentially any value of Region from Table2 that has a row without a corresponding value in Table1 ends up in the result paired up with a blank Category due to the foreign key violation.

If for example you add another row in Table2 - E, UK, 5 then the visual will show 2 rows with blank Category. 
However, if you use the Category from Table2 to pair up with Region from Table2, we won't cross table boundaries and the foreign key violation will not show up.

The behavior of inner join will only happen if a constraint from Table1 is added. For example add SUM(Table1[Amount]) to the visual and that will end up constraining the values. Since the Category values from Table2 that do not show up in Table1 won't have any Amount value, they are not present in the visual.

 

 

Best Regards,
Qiuyun Yu