Forum Discussion
DAX - RANKX with Partition By
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 exam...
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
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
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))
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
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 ) )