Forum Discussion
Dynamic ranking based on date
Hi all,
Request your help on this.
We are basically having a table with Name,Date and based on date we are assigning Rank.
We have calculated column to calculate rank:RANKX(FILTER(Table1,Table1[Name]=EARLIER(Table1[Name])),Table1[Date],,DESC,Dense)
But when we are changing date filter i.e going to back date rank is not getting updated accordingly.For e.g for A when we changed date filter from 15 feb to 8 Feb then A on 3rd Feb will be having rank as 1 but still it remains 2.
Please can we have a solution for this.
Thanks,
saranee
Try this MEASURE for dynamic RANKING
RANK = RANKX ( FILTER ( ALLSELECTED ( Table1 ), Table1[Name] = SELECTEDVALUE ( Table1[Name] ) ), CALCULATE ( SELECTEDVALUE ( Table1[Date] ) ), , DESC, DENSE )
11 Replies
HI Richard,
We will be then unable to use EARLIER in measure.Please can you suggest some alternative as we want seperate set of names and then trying to find rank.
Thanks,
saranee
Hi saranee,
Try this code:
_Index =
IF(ISBLANK(CALCULATE(COUNTROWS(Table2); FILTER(ALLSELECTED(Table2[Date]); Table2[Date] < MAX(Table2[Date]))));
1;
CALCULATE(COUNTROWS(Table2); FILTER(ALLEXCEPT(Table2; Table2[Name]); Table2[Date] < MAX(Table2[Date]))) + 1)Ricardo
Hi,
Try this measures
Date value=SUM(Table1[Date])
Rank=RANKX(ALL(Table1[Date]),[Date value])
If this does not help, then share the link from where i can download your file.
Hello,
I want to create a new column ranking on the basis of certain values in a column (AHT). However, I want the ranking to be filtered according to the Date. I mean it should rank the values in AHT, if I filter the date to Jan-22 then it should give me ranking on the basis of date and not for overall dates.
Ecode Ename AHT Date 45654 A 382 Jan-22 45655 B 289 Jan-22 45654 A 351 Feb-22 Hi,
Download my PBI file from here. Let me know if this is working well.
