Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear All, I have a data as below and i would like to calculate the ranks based on RNO_Condition.
Data:-
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc07CoUwEEDRrTymlpCZ/LC0faAWlhKCYEBBYooUunsTVLC9p7jjCCiMNC74HSoQSJwpAlu9vU9L7sgFVwzFB4a4hmkrVhtG6haJWrluT792itHPRUkzTR9twrH6dGYiw58uEd0/lX++SLD2Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [RNO_Condition = _t, Amt = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"RNO_Condition", type text}, {"Amt", type number}})
in
#"Changed Type"
I have written below dax and based on that i am getting the below shown output
But i am expecting the ranking as below image.
Any help please.
Thanks,
Mohan V.
Solved! Go to Solution.
Hi, @Anonymous , as you want to rank the entries within a sub-group, I'd suggest adding a column, whether in PQ or by calculated column, to distinguish different sub-group.
#"Extract Prefix" = Table.AddColumn(#"Changed Type", "Category", each Text.Start([RNO_Condition], 5))
or using calculated column
The measure is
Ranks =
VAR __cat = MAX ( 'Dataset'[Category] )
RETURN
RANKX (
FILTER ( ALL ( 'Dataset' ), 'Dataset'[Category] = __cat ),
CALCULATE ( MAX ( 'Dataset'[Amt] ) )
)
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! |
Hi, @Anonymous , as you want to rank the entries within a sub-group, I'd suggest adding a column, whether in PQ or by calculated column, to distinguish different sub-group.
#"Extract Prefix" = Table.AddColumn(#"Changed Type", "Category", each Text.Start([RNO_Condition], 5))
or using calculated column
The measure is
Ranks =
VAR __cat = MAX ( 'Dataset'[Category] )
RETURN
RANKX (
FILTER ( ALL ( 'Dataset' ), 'Dataset'[Category] = __cat ),
CALCULATE ( MAX ( 'Dataset'[Amt] ) )
)
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! |
Is your calculation creating groups first on the basis of the Prefix Number in RNO_Condition and then ranking on basis of Amt?
@Anonymous Check this.
https://community.powerbi.com/t5/Desktop/DAX-Ranking-within-a-group/td-p/261079
Let me know if you still needs help. I will write the DAX Expression for you.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
98 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |