Show Items with no data filtering behaviour

[Jeremie]

I've noticed that the "Show items with no data" has changed the way filtering works.  Suppose I have two tables, fact and dim.  Fact contains the values 3,4,5,5 and dim contains 1,2,3.  They are related with a one (dim) to many (fact) relationship with a single cross filter direction.

The show items with no data option will show items that are in the dim but not in the fact.  Namely; 1,2.  That seems reasonable.

So I can filter on the blanks in the dimension to get everything in that fact that's missing.  However, if I wanted to do the opposite, the empty items in fact are not really blank.  If I try and use the filter to return "blanks" from the fact table, why does it return all values from the dim table?

I know I could create a calculated value that finds the number of related items in the fact table, effectively doing what I'm asking for.  I would like to understand why powerBI behaves this way, and if it's a bug or a feature.  In the past, I had frequently been using a relationship between two tables and filtering one way or the other to quickly access data quality.

Regards,

Jeremie

[v-jiascu-msft · Accepted solution]

@Jeremie,

Hi Jeremie,

That's the function of "Cross filter direction". If you change it into "both", the result would be right. (part 2 of the snapshot)  powerbi-desktop-create-and-manage-relationships

>>>Both - This is the most common, default direction. This means for filtering purposes, both tables are treated as if they're a single table.  

To be simple, Power BI can't filter dim table from fact table to dim table. 

Best Regards!

Dale

[Jeremie]

Thanks Dale,

The cross filter direction doesn't really answer my question, however your comment that "Power BI can't filter dim table from fact table to dim table." does. 

thanks