Forum Discussion

drivas771994's avatar
drivas771994
Icon for Helper II rankHelper II
8 years ago

Filter 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 th...
v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
8 years ago

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.

https://www.dropbox.com/s/9c8hl85e8qnfyjo/Filter%20the%20Same%20Value%20in%20two%20different%20ways.pbix?dl=0

 

Best Regards,

Lin