Forum Discussion
Show Items with no data filtering behaviour
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
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
2 Replies
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
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
