Forum Discussion

mjmt1994's avatar
mjmt1994
Frequent Visitor
7 years ago
Solved

Ignore slicer selection with DAX

Hello all,

 

I have a page where I use different table visualization/slicer combination to display a series of performance measures for my company. One slicer interacts with one and only one table visualization. In the end, I want to get all those percentages with their current slicer filter  to measure their average. However, I can't do so straight away because for the final table visualization, in which I display the averages, all the slicers are interacting, producing a different calculation than the one I want. With this in mind, I can't use Edit Interactions for such scenario. What I have to do is to find a DAX way to calculate the average and making sure that each measure is filtered according to the one slicer, ignoring the others. (I am sorry for hiding all the information)

 

 

 

I have been struggling with ignoring some slicer selections in a DAX Measure. I have tried to use the ALL, ALLEXCEPT, IN VALUES but without success. Are those functions expected to work in such scenario? Is there a straightforward solution or a workaround to solve my problem?

 

The example of the code I'm using (without wrapping in CALCULATE with the above mentioned functions). The idea was to use something like ALLEXCEPT(Table, Table[Column referenced for that percentage]).

 

 

Sum Evaluation Average = 
IF([Percentage 1] <> BLANK(), [Percentage 1], 0) + 
IF([Percentage 2] <> BLANK(), [Percentage 2], 0) +
IF([Percentage 3] <> BLANK(), [Percentage 3], 0) +
IF([Percentage 4] <> BLANK(), [Percentage 4], 0) + 
IF([Percentage Within 5] <> BLANK(), [5], 0) + 
IF([Percentage Within 6] <> BLANK(), [Percentage 6], 0) +
IF([Percentage Within 7] <> BLANK(), [Percentage 7], 0)

 

 Kind Regards

 

  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi mjmt1994

    Please look at my test for example

    Create measures

    per is the percentage for each row
per = DIVIDE(MAX([value]),MAX([total]))

average per is the average percentage as you expected,
average per = SUMX(ALLEXCEPT(Sheet2,Sheet2[category]),[per])/
                  CALCULATE(DISTINCTCOUNT(Sheet2[flag]),ALL(Sheet2))

     

    The final table should not be filtered by any slicer on the page.

     

    Best Regards

    Maggie

3 Replies

    • mjmt1994's avatar
      mjmt1994
      Frequent Visitor
      7 years ago

      Hi,

       

      Thanks for the quick response!

       

      That's basically it, yes.

       

      Is there a solution for it?

       

      Kind Regards

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi mjmt1994

    Please look at my test for example

    Create measures

    per is the percentage for each row
per = DIVIDE(MAX([value]),MAX([total]))

average per is the average percentage as you expected,
average per = SUMX(ALLEXCEPT(Sheet2,Sheet2[category]),[per])/
                  CALCULATE(DISTINCTCOUNT(Sheet2[flag]),ALL(Sheet2))

     

    The final table should not be filtered by any slicer on the page.

     

    Best Regards

    Maggie