Forum Discussion
Unusual Filter Behavior - Filter Context Not Being Removed as Expected
I have a Sales fact table with the following fields:
[Sales Amount]
[Sales Quantity]
[Item]
[Item Revision]
[Sales Period] (a text field in the format of YYMM)
[Customer]
[Sales Region]
I have a measure:
[Sales Total] := SUM(Sales[Sales Amount])
and another measure
[Sales Total All Items] := CALCULATE(
SUM(Sales[Sales Amount]),
ALL(Sales[Item], Sales[Revision])
)and finally
[Item Revision % of Total Sales] := DIVIDE(
[Sales Total],
[Sales Total All Items])
I have a page-level filter on [Sales Region], and I have a slicer on [Customer] and [Period]. I also have a stacked bar chart visual with [Sales Amount] by [Item]. Finally, I have two tables: one with data aggregated by [Period], and one with transaction-level data.
The problem is that when I have more than one [Customer]s selected in my slicer, and I filter by [Item] using my visual, my [Sales Total All Items] measure will sum the rows only where [Customer] purchased the filtered [Item].
For example, if I had two [Customers], "A" that purchased the selected [Item], and "B" that didn't, [Sales Total All Items] will only reflect the total of all items from "A." Likewise, if I filter on another [Item] that both had purchased, [Sales Total All Items] will reflect the same amount as if I had not applied any filters on [Item].
If I "Select All" in my [Customer] slicer, I can filter on [Item] and get an appropriate total across all [Customers]. Likewise, when I de-select my [Item] filter in my visual, I once again get a correct total in my aggregate table.
When I filter on [Sales Period] and apply the same [Item] filter, I see the same behavior: [Sales Total All Items] only provides a total for the [Sales Period]s in which there's a record for the filtered [Item].
To summarize, even though my measure is using CALCULATE( . . ., ALL([Item]) ) to remove filter context, it still seems to be interacting with my other filters. I don't really understand this behavior, and I don't know how to get around it.
I appreciate any help.
- Anonymous7 years ago
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.