topic Re: DAX - RANKX with Partition By in DAX Commands and Tips

DAX - RANKX with Partition By

Anonymous — Fri, 11 Mar 2022 16:54:39 GMT

Hi,

I am trying to create a rankx measure that will create a row number by date but also partitioned by category. This will also dynamically update when the user changes the date filter. For example:

Category	Date	rn
a	01/01/2022	1
a	02/01/2022	2
a	03/01/2022	3
b	01/01/2022	1
c	02/01/2022	1
d	03/01/2022	1

If the user then change the date slicer to 02/01/2022 - 03/01/2022 the result would be:

Category	Date	rn
a	02/01/2022	1
a	03/01/2022	2
c	02/01/2022	1
d	03/01/2022	1

Any help would be most welcome!

Re: DAX - RANKX with Partition By

OwenAuger — Sat, 12 Mar 2022 02:37:55 GMT

Hi Anonymous

Something like this (replace Table references as necessary):

rn = VAR CurrentDate = SELECTEDVALUE ( YourTable[Date] ) VAR RankingTable = CALCULATETABLE ( SUMMARIZE ( YourTable, YourTable[Date] ), ALLSELECTED (), -- filter context of visual VALUES ( YourTable[Category] ) -- retain current Category filter ) RETURN RANKX ( RankingTable, YourTable[Date], CurrentDate, ASC )

Regards,

Owen

Re: DAX - RANKX with Partition By

CNENFRNL — Sat, 12 Mar 2022 21:43:16 GMT

For fun only, the logical is replicated in Excel,

Re: DAX - RANKX with Partition By

Anonymous — Mon, 14 Mar 2022 09:53:28 GMT

Thanks OwenAuger, this worked.

Re: DAX - RANKX with Partition By

Anonymous — Mon, 14 Mar 2022 09:53:55 GMT

Thanks CNENFRNL, this worked also.

Re: DAX - RANKX with Partition By

Anonymous — Mon, 14 Mar 2022 10:55:23 GMT

Hi OwenAuger, after getting the row number I am trying to count the categories where the row number = 1 which I thought would be a simple calculate function:

Count RN =

CALCULATE(
COUNT(Table, Table[Category]), FILTER(Table,'_DAX Measures'[_rn] = 1))

This doesn't seem to work. Do you know how to get round this?

Thanks in advance

Re: DAX - RANKX with Partition By

OwenAuger — Mon, 14 Mar 2022 12:30:51 GMT

I'm thinking something like this, if you want to count the number of times [_rn]=1 in that particular visual, assuming you're placing this as a standalone measure outside the original visual.

Count RN = SUMX ( SUMMARIZE ( Table, Table[Category], Table[Date] ), IF ( [_rn] = 1, 1 ) )