Ignore slicer selection with DAX

[mjmt1994]

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 · Accepted solution]

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

[v-juanli-msft]

Hi @mjmt1994

I make test, is my understanding right?

Best Regards

Maggie

[mjmt1994]

Hi,

Thanks for the quick response!

That's basically it, yes.

Is there a solution for it?

Kind Regards