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

Hi marcoselias,  

The sample data used for reporting the issue is below: 

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