Need help with EXCEPTALL

[cmazur71]

In the below meaure, the EXCEPTALL is not keeping my filters for [Comparison1] [Group] [Region]   

[Comparison1] is a dropdown slicer, while [Group] and [Region] are vertical lists.

CUT_COMP1 =

VAR CutComp1 = CALCULATE(SUM('Data'[Cut Compacted]), ALLEXCEPT('Data','Data'[Comparison1], Data[Group], Data[Region]))

Anyone know why EXCEPTALL is not keeping the filter values from the slicer?

Here is my pbix:

https://sendit.hexagon.com/link/jjdzi6Xrpt1SZHV5G8oseH

[v-xianjtan-msft]

Hi @cmazur71 

The problem is not with the ALLEXCEPT() function in DAX, the real reason is that the slicers are not set up correctly to interact with card visuals.

In your report, select Format > Edit interactions. You can see that the slicers “Comparison1” and “Comparison2” only cross-filter the table visual below each, so the results of your card visual are not affected by these two slicers.

Please follow the screenshot below to change your interaction settings:

Please refer to the following links to learn more about visual interaction:

Change how visuals interact in a report - Power BI | Microsoft Learn

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hnguy71]

Hi @cmazur71 

What's the expected numbers that you are looking for? 

[cmazur71]

When I use Edit Interactions, I can get the cards to show the correct numbers, but when I create this new measure it is not correct:

CUT_CHANGE = [CUT_COMP2] - [CUT_COMP1]

CUT_CHANGE should be 5773 - 25069 = -19296

But when I create a card showing CUT_CHANGE, the quantity is 0

[v-xianjtan-msft · Accepted solution]

Hi @cmazur71 

I think the CUT_CHANGE measure calculation is not showing correctly because it is receiving filter conditions from two fields from the same table in the same measure, for example in your screenshot: it is required to satisfy both Comparison1 equal to Subgrade vs. Topo 4-21 and Comparison2 equal to Subgrade vs. Topo 6-16, which is not possible in the same table.

I would suggest that you consider creating a calculation table that contains all the data for COMP2, thus calculating COMP1 and COMP2 separately, and that would work. Remember to restore the Edit Interaction settings if you use this method.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.