Forum Discussion
Unusual Filter Behavior - Filter Context Not Being Removed as Expected
I finally found a solution to this problem after encountering it again in a new problem set. This post on the forums:
led to this enlightening article:
https://www.sqlbi.com/articles/understanding-dax-auto-exist/
Long story short, when applying multiple filters from the same table, Power BI optimizes by consolidating what would be multiple queries into one. The resultant table will only include results for records that have that have that particular combination of filters; if a particular combination of values for those two (or more) fields didn't exist in your filtered set, CALCULATE( . . . ALL([Fieldname] ) won't get them back for you!
The recommendation is to use a dimensional model (e. g., star schema) to avoid filtering a table by its own fields.
I finally found a solution to this problem after encountering it again in a new problem set. This post on the forums:
led to this enlightening article:
https://www.sqlbi.com/articles/understanding-dax-auto-exist/
Long story short, when applying multiple filters from the same table, Power BI optimizes by consolidating what would be multiple queries into one. The resultant table will only include results for records that have that have that particular combination of filters; if a particular combination of values for those two (or more) fields didn't exist in your filtered set, CALCULATE( . . . ALL([Fieldname] ) won't get them back for you!
The recommendation is to use a dimensional model (e. g., star schema) to avoid filtering a table by its own fields.