Unusual Filter Behavior - Filter Context Not Being Removed as Expected

[Anonymous]

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.

[Anonymous · Accepted solution]

I finally found a solution to this problem after encountering it again in a new problem set. This post on the forums:

https://community.powerbi.com/t5/Desktop/Make-measure-ignore-specific-filter/m-p/670629/highlight/tr...

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.

[v-danhe-msft]

Hi @Anonymous,

Based on my test, you could modify your [Sales Total All Items] measure as below:

Sales Total All Items = CALCULATE(
    SUM(Sales[Sales Amount]),
    ALL(Sales))

Result:

Regards,

Daniel He

[Anonymous]

Hi @v-danhe-msft,

I posted a substantial reply that seems to have disappeared. I will try to briefly summarize the issue.

In your example, I'm seeing a total of [Sales Amount] for the entire Sales table. Instead, I want a total of [Sales Amount] with any filtering on [Item] and [Revision] removed, but with the other filters maintained. In your example, I would want a total of 10+20 = 30, not 60, since [Customer] L is not selected.

I assumed that CALCULATE(. . . , ALL(Sales[Item], Sales[Revision]) would calculate my measure with those two filters removed, but my total doesn't reflect that any filters are removed.

I have also tried using ALLEXCEPT(Sales, Sales[Customer], Sales[Sales Period], Sales[Sales Region]), but this too doesn't behave exactly right. With no [Item] filter selected, this will actually return a proper total. Once you select an [Item] filter (e. g., using your visual), this solution may fail if more than one filter is selected for [Customer], [Sales Period], etc..

Using your image, let's assume that [Item] 2 (through the visual, not the table) and [Customer]s M and N are selected as filters. Let's also assume a new row for [Customer] N, but instead of [Item] 2, it's [Item] 1; everything else is identical.

The behavior I'm seeing is that my total will be 20+20 = 40. I would expect 10+20+20 = 50, the sum of all [Sales Amount] given active filters except for [Item] and [Revision].

As far as I can tell, the ALLEXCEPT() codes behaves something like this:

• Filter the Sales table by [Customer] and [Item] per the slicer and visual, respectively
• This leaves a table with one record, [Customer] N and [Item] 2
• CALCULATE() then removes filter context from [Item]
• This exposes the row with [Customer] N and [Item] 2
  • But it does nothing for the row with [Customer] M, since this row did not exist in the "original" table
• The sum is then calculated on this table

The behavior that I was expecting goes something like this:

• Filter the Sales table by [Customer] and [Item] per the slicer and visual, respectivel.
• This leaves a table with one record, [Customer] N and [Item] 2
• CALCULATE() then removes filter context from [Item]
• This leaves the original Sales table with only the [Customer] filter applied
• The sum is then calculated on this table

To summarize:

• CALCULATE(. . ., ALL(Sales[Item], Sales[Revision])) does not seem to remove any filters.
• CALCULATE(. . ., ALLEXCEPT(Sales[Customer], Sales[Sales Period], Sales[Sales Region]) will provide a correct total in two cases:
  • No [Item] filter is selected.
  • If an [Item] filter is selected, there must exist a row containing the filtered [Item] for each combination of [Customer] and [Sales Period]; otherwise, no rows from that [Customer] / [Sales Period] combination will be included in the total.
    • If, for example, one selected [Customer] failed to order the selected [Item] for one month, no [Item]s for that month from that [Customer] will make it into the total.
    • If, as another example, one selected [Customer] never ordered the selected [Item], no [Item]s ordered by that [Customer] will be added to the total.

Any help would be appreciated.

[Anonymous]

Hi @v-danhe-msft,

The issue with using ALL(Sales) is that this will evaulate the measure without filter context on [Customer], [Sales Region], and [Sales Period], as well. I need this measure to maintain filters on those fields, and ideally, on any arbitrary fields but [Item] and [Revision], in case I want to extend functionality to include other filters.

In your screenshot, I would want to see 10+20 = 30, not 60, since your slicer does not include [Customer] L.

Assume I create an alternate measure:

[Total Sales All Items 2] := CALCULATE(
    SUM(Sales[Sales Amount]),
    ALLEXCEPT(Sales, Sales[Customer], Sales[Sales Period], Sales[Sales Region])
)

I still see similar behavior to my original measure: both only include rows containing [Customer] if there is also a row containing the same [Customer] and the [Item] that is being filtered by the visual.

To use your example, assume your current selection: [Customer]s M and N and [Item] 2. Also assume that you use your [Item] visual to filter the report to [Item] 2.

In this case, the [Sales Total All Items] and [Sales Total All Items 2] sums will be 20, when 30 would be expected. Since [Customer] M did not purchase [Item] 2, that row's total will not be counted.

Assume you had another row containing [Customer] N and [Item] 1 with a [Sales Amount] of 100. These totals would instead be 120, when the desired result is 130. Note that even though [Item] 1 is excluded by the current [Item] filter, [Customer] N is not, so this row is still included in the measure. This doesn't feel right, since a measure shouldn't have row context, but at the very least, this row isn't included in the table over which our measure is being calculated.

For whatever reason, CALCULATE(ALL(Sales[Item], Sales[Revision])) doesn't seem to be removing any filters whatsoever. If you plug [Total Sales All Items] into your table, it will always equal [Sales Amount], when the desired behavior is that it will be the total of all [Item]s with respect to any [Customer], [Sales Period], and [Sales Region] filters.

The ALLEXCEPT() behavior is truly baffling to me. If you plug [Total Sales All Items 2] into your table, as long as you have no [Item] filters selected, you will get the correct total of all [Item]s with respect to the other filters. Once you select an [Item] filter, however, the behavior changes entirely.

To return to and extend my earlier example, assume we add 2 rows:

• [Customer] N and [Item] 1 with a [Sales Amount] of 100
• [Customer] L and [Item] 1 with a [Sales Amount] of 100

Assume the other fields are identical to the data in your table, since they shouldn't be relevant.

In our current filter context, the only "visible" row is the one with [Customer] N and [Item] 2. CALCULATE(. . ., ALLEXCEPT()) removes the filter context from [Item], so now we can "see" the row with [Customer] N and [Item] 1; however, since we're maintaining filters on [Customer], [Customer] L remains filtered out.

Essentially, the engine seems to work this way:

1. The table is generated by all current filter context
2. Filter context is removed by CALCULATE()

And I need it to work this way:

1. Filter context is removed by CALCULATE()
2. The table is generated by the new current filter context

Of course, none of this would be an issue if ALL(Sales[Item], Sales[Revision]) removed filter context as expected, but it doesn't appear to be doing anything. I'm not sure if this is an issue with my data set, or if there's a logical error I'm not understanding.