Forum Discussion

cmazur71's avatar
cmazur71
New Member
1 year ago
Solved

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

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

4 Replies

    • cmazur71's avatar
      cmazur71
      New Member
      1 year ago

      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

       

       


       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.