topic Re: Row Number in DAX in DAX Commands and Tips

Row Number in DAX

Anonymous — Sun, 19 Jul 2020 10:56:04 GMT

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 🙂

Re: Row Number in DAX

az38 — Sun, 19 Jul 2020 12:23:12 GMT

Anonymous

use rankx like

Column = RANKX( FILTER( 'Table', 'Table'[CommitteeName]=EARLIER('Table'[CommitteeName]) && 'Table'[AllotmentMainID]=EARLIER('Table'[AllotmentMainID]) ), 'Table'[ApprovedTotalLimit], , DESC )

Re: Row Number in DAX

Anonymous — Sun, 19 Jul 2020 13:41:39 GMT

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

Re: Row Number in DAX

az38 — Sun, 19 Jul 2020 13:56:39 GMT

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 )

Re: Row Number in DAX

amitchandak — Mon, 20 Jul 2020 02:52:43 GMT

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

Re: Row Number in DAX

sqldev2017 — Thu, 25 Mar 2021 05:35:37 GMT

I think DAX is utter nonsense, compared to the simplicity of SQL !