topic Identifying the top value and any value 10 less than the top value in DAX Commands and Tips

Identifying the top value and any value 10 less than the top value

Anonymous — Sun, 07 Feb 2021 12:09:44 GMT

I am trying to find the easest way to write a DAX measure to carry out the following action

I have a table with the following columns

(Name), (Age), (RPR)

John, 23, 133

Alan, 25, 144

David, 43 , 145

Philip, 47, 151

Brian, 26, 134

Tony, 22, 126

Steven, 33, 145

I want to find the top value from column (RPR) and then return True or False of the Value in (RPR) if it is the top value or the top value less 8, so the table should look like the following (any value from 143 to 151 should output 'True' any other value 'False'

(Name), (Age), (RPR), (Top or 8 less)

John, 23, 133, False

Alan, 25, 144, True

David, 43 , 145, True

Philip, 47, 151, True

Brian, 26, 134, False

Tony, 22, 126, False

Steven, 33, 145, True

I then once have the 'True or 'False statement want to turn those True & False statements into integers True=8 and false=0 and display the table as below

(Name), (Age), (RPR), (Top or 8 less) (Score)

John, 23, 133, False, 0

Alan, 25, 144, True, 8

David, 43 , 145, True, 8

Philip, 47, 151, True, 8

Brian, 26, 134, False, 0

Tony, 22, 126, False, 0

Steven, 33, 145, True, 8

Is there any one who knows the simplest way to do this?

Thanks for your time

Re: Identifying the top value and any value 10 less than the top value

CNENFRNL — Sun, 07 Feb 2021 12:34:27 GMT

Hi, Anonymous , you might want to try this formula in a calculated column,

Score = ( Table1[RPR] >= MAX ( Table1[RPR] ) - 8 ) * 8

Re: Identifying the top value and any value 10 less than the top value

Anonymous — Sun, 07 Feb 2021 14:40:44 GMT

Thanks for your response, in my table that doesnt work for some reason, im getting all 0's

so I took out the -8 ) *8 and the result reads 'false' in every row, hence all the zeros

If I can just get the correct 'true' or 'false' statement first I can convert that to integers and multiply by 8 quite easily.

I was looking for a DAX method via a measure rather than a calculated column.

Thanks for you time.

Re: Identifying the top value and any value 10 less than the top value

Anonymous — Sat, 13 Feb 2021 19:21:30 GMT

Apologies ! Your solution does work, there was an error in my table,

Thanks