Forum Discussion
Can you use RANKX with a date filter?
The information you have provided is not making the problem clear to me. Can you please explain with an example.
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
Hi,
Below is a dummy data that hopefully will help:
| UK | France | Germany | Rank | ||||
| 01/04/2020 | 10 | 0 | 60 | 2 | 3 | 1 | |
| 02/04/2020 | 20 | 0 | 60 | 2 | 3 | 1 | |
| 03/04/2020 | 30 | 0 | 60 | 2 | 3 | 1 | |
| 04/04/2020 | 40 | 0 | 60 | 2 | 3 | 1 | |
| 05/04/2020 | 50 | 100 | 70 | 3 | 1 | 2 | |
| 06/04/2020 | 60 | 120 | 70 | 3 | 1 | 2 | |
| 07/04/2020 | 70 | 120 | 70 | 2 | 1 | 2 | |
| 08/04/2020 | 80 | 130 | 70 | 2 | 1 | 3 | |
| 09/04/2020 | 90 | 135 | 70 | 2 | 1 | 3 | |
| 10/04/2020 | 100 | 145 | 70 | 2 | 1 | 3 | |
| 11/04/2020 | 110 | 145 | 70 | 2 | 1 | 3 | |
| 12/04/2020 | 110 | 160 | 70 | 2 | 1 | 3 | |
| 13/04/2020 | 110 | 160 | 70 | 2 | 1 | 3 |
As you can see from the above over time the ranking of the countries changes (all sales values are cumulative). My RANKX is currently producing the rankings as per the rank in the right hand side of the table. I am then using another measure to return a 1 if the country is in my Top N (I created another table and just populated 1 to 10 in this) or a 0 if it is not. I then use this measure to filter my visuals.
So at the moment if I used this to show me the Top 1 country by sales over time it would plot 2 lines one for France and one for Germany as they have both been ranked 1 at a particular point in time. Whereas my aim would be that the RANKX would only produce a rank as at the latest date which would be todays date minus 1 or in the above data set 13/04/2020. This way it would then only show one line of France.
Not sure if this is possible but I am intrigued to find out.
Thanks.