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.
Hi,
My SQL query is this ;
SELECT
CommitteeName,
AllotmentMainID,
ApprovedTotalLimit,
ROW_NUMBER() OVER(PARTITION BY CommitteeName,AllotmentMainID ORDER BY ApprovedTotalLimit DESC) AS RN
FROM Allotment;
I want to write in Dax. How to write Row Number in Dax?
Please Help.
Thanx 🙂
@Anonymous
use rankx like
Column =
RANKX(
FILTER(
'Table',
'Table'[CommitteeName]=EARLIER('Table'[CommitteeName]) && 'Table'[AllotmentMainID]=EARLIER('Table'[AllotmentMainID])
),
'Table'[ApprovedTotalLimit], , DESC
)
I think DAX is utter nonsense, compared to the simplicity of SQL !
Thank you for your answer but I I could not tell sorry.
For Example :
CommitteeName | AllotmentMainID | ApprovedTotalLimit | |
A | 100 | 2500 | |
A | 100 | 2500 | |
A | 100 | 3000 | |
B | 200 | 5000 | |
C | 300 | 10000 | |
C | 300 | 5000 |
Result :
CommitteeName | AllotmentMainID | ApprovedTotalLimit | RowNumber |
A | 100 | 3000 | 1 |
A | 100 | 2500 | 2 |
A | 100 | 2500 | 3 |
B | 200 | 5000 | 1 |
C | 300 | 10000 | 1 |
C | 300 | 5000 | 2 |
I want to this.
Thank U:)
@Anonymous , refer this how to break ties
https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
@Anonymous
how do you want to break a tie-situations?
you can add an Index column in powe query and the create a dax rankx calculated column
Column =
RANKX(
FILTER(
'Table',
'Table'[CommitteeName]=EARLIER('Table'[CommitteeName]) && 'Table'[AllotmentMainID]=EARLIER('Table'[AllotmentMainID])
),
'Table'[ApprovedTotalLimit] + [Index]/1000000, , DESC, Skip
)
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |