cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tonyclifton Regular Visitor
Regular Visitor

Measure to filter product by highest rating

Hello community,

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


Output:
s.PNG

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
tonyclifton Regular Visitor
Regular Visitor

Re: Measure to filter product by highest rating

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 6
adetogni Member
Member

Re: Measure to filter product by highest rating

hi

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

tonyclifton Regular Visitor
Regular Visitor

Re: Measure to filter product by highest rating

yes @adetogni 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.

adetogni Member
Member

Re: Measure to filter product by highest rating

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 Regular Visitor
Regular Visitor

Re: Measure to filter product by highest rating

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
adetogni Member
Member

Re: Measure to filter product by highest rating

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!

adetogni Member
Member

Re: Measure to filter product by highest rating

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).