cancel
Showing results for
Did you mean:
Highlighted
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.

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.

1 ACCEPTED SOLUTION

Accepted Solutions
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
Member

## Re: Measure to filter product by highest rating

hi

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

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.

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

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```
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!

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