Forum Discussion
Helper IIFilter the Same Value in two different ways
Hi,
Above is a picture of a report I am working on. The premise of the report is to compare an athlete's individual score to the teams Max and Minimum and show where that falls using the gauge graph. Currently, I am having trouble getting the Max score and the Min score to stay filtered to the entire team's scores. Instead what is happening is that when I select an athlete name. The gauge is filtering itself to only that athlete's scores. Is there a way to get the min and max of the gauge to stay filtered as the entire team but have the score in the middle be that individual filtered athlete's score?
6 Replies
Use ALL() when calculate the max and min.
MAX = CALCULATE(MAX('Score'[Games);ALL('Score'[Games]))
Using this function, the calculate ignores the filters.
drivas771994This formula did not work. It still filtered by the individual athlete.
- PattemManohar
Community Champion
Just wondering, whether you have tried "Visual Level Filters" or not....
- drivas771994
I tried to but it does not allow me to filter the values based on text or by the athlete name
- drivas771994
Also what if I wanted that filtered by a specific team and date range
v-lili6-msftCommunity Support
HI,@drivas771994
After my research, you can use ALLEXCEPT Function in the formula like this:
for example
Assume that i a data as below:
then use these formula:
Team max = CALCULATE(MAX(Table1[Score]),ALLEXCEPT(Table1,Table1[Sport])) Team min = CALCULATE(MIN(Table1[Score]),ALLEXCEPT(Table1,Table1[Sport])) Person max = CALCULATE(MAX(Table1[Score]),ALLEXCEPT(Table1,Table1[Sport],Table1[Name])) Person min = CALCULATE(MIN(Table1[Score]),ALLEXCEPT(Table1,Table1[Sport],Table1[Name]))
when I select sport is "football" and name is "B"
Result:
here is pbix,please try it.
Best Regards,
Lin
