Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
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
Solved! Go to Solution.
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.
Apologies ! Your solution does work, there was an error in my table,
Thanks
Apologies ! Your solution does work, there was an error in my table,
Thanks
Hi, @Anonymous , you might want to try this formula in a calculated column,
Score = ( Table1[RPR] >= MAX ( Table1[RPR] ) - 8 ) * 8
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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.
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |