Forum Discussion
Helper IIIMeasure to filter product by highest rating
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.
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
6 Replies
tonycliftonyes 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.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
