Measure to filter product by highest rating

[tonyclifton]

Hello community,

I need to create a measure to filter below table by product and its highest rating.

Output:

This measure seems fine:

Max = CALCULATE(MAX(Table1[Rating]);ALLEXCEPT(Table1;Table1[ProductID]))

However I need to show the Rating column, so I am guessing an IF statement is needed here but I cannot get it to work.

Can you help?

Thank you.

[Anonymous]

hi

the "output" that you show is the desidered output?

[tonyclifton]

yes @Anonymous that's the desired output.
My Idea is to have a measure that is either 1 or 0 for each product and rating so that I can use it in a page level filter.

[Anonymous]

ok so the best way to do so is to use a calculated column with the RANKX dax. You won't have "1 or 0" but a number from 1 to N where 1 is the highest

Create a custom column and use this formula (i assume that the grouping is at productId level)

Ordering = 
VAR thisProductId = YourTableName['ProductID']

RETURN
RANKX(FILTER(YourTableName;YourTableName['ProductID']=thisProductId);YourTableName['Rating'];DESC)

you should have a number from 1 to N where 1 is the highest value in each group

[tonyclifton · Accepted solution]

I couldn't get the rank part to work, so in the end I was able to do it like this:

isMax = 
var a = CALCULATE(MAX(Table1[Rating]);ALLEXCEPT(Table1;Table1[ProductID]))

var x = if(a <= Table1[Rating];1;0)
return x

[Anonymous]

Hi

it should work, the logic is the same, but with this method you will get only 0-1, whereas in my method it's 1 to N.
Still, that's what you need so good job!

[Anonymous]

in fact the formula i wrote won't work, I realize now, since it isnt' wrapped in a CALCULATE (so the row context does not switch to a filter context).