Reply
cmazur71
New Member

Need help with EXCEPTALL

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

 

 

1 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.

vxianjtanmsft_0-1730698889969.png

 

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.

View solution in original post

4 REPLIES 4
v-xianjtan-msft
Community Support
Community Support

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.

vxianjtanmsft_3-1730447078916.png

 

Please follow the screenshot below to change your interaction settings:

vxianjtanmsft_1-1730446849181.pngvxianjtanmsft_2-1730446935769.png

 

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
Super User
Super User

Hi @cmazur71 

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



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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

 

cmazur71_1-1730471358427.png

 


 

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.

vxianjtanmsft_0-1730698889969.png

 

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.

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)