Row Number in DAX

[Anonymous]

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 🙂

[az38]

@Anonymous 

use rankx like

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

[sqldev2017]

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

[Anonymous]

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

[amitchandak]

@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

[az38]

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