Forum Discussion
Change Rank Dynamically by user selected Filter
Try to add an index column and create a measure as below.
RANK = MINX ( FILTER ( SELECTCOLUMNS ( ALLSELECTED ( ParentData ), "index", ParentData[Index], "rank", RANKX ( ALLSELECTED ( ParentData ), ParentData[Turn Around Time],, DESC, DENSE ) ), [index] = MAX ( ParentData[Index] ) ), [rank] )
Thanks for the prompt reply, the ranking needs to be changed according the filters applied by the user at the runtime
Lets consider the below scenario
No Filters Selected
| Sr No | Customer Name | Turn Around Time | Date | Rank |
| 1 | XYZ | 10 | 16-Sep | 5 |
| 2 | XYZ | 5 | 16-Sep | 6 |
| 3 | XYZ | 21 | 17-Sep | 2 |
| 4 | ABC | 20 | 16-Sep | 3 |
| 5 | ABC | 25 | 16-Sep | 1 |
| 6 | ABC | 15 | 17-Sep | 4 |
| 7 | ABC | 10 | 17-Sep | 5 |
| 8 | ABC | 20 | 17-Sep | 3 |
When Selected Filter : Customer Name = ABC
| Sr No | Customer Name | Turn Around Time | Date | Rank |
| 1 | XYZ | 10 | 16-Sep | |
| 2 | XYZ | 5 | 16-Sep | |
| 3 | XYZ | 21 | 17-Sep | |
| 4 | ABC | 20 | 16-Sep | 2 |
| 5 | ABC | 25 | 16-Sep | 1 |
| 6 | ABC | 15 | 17-Sep | 3 |
| 7 | ABC | 10 | 17-Sep | 4 |
| 8 | ABC | 20 | 17-Sep | 2 |
Filter : Customer Name = ABC & Date = 17 Sep
| Sr No | Customer Name | Turn Around Time | Date | Rank |
| 1 | XYZ | 10 | 16-Sep | |
| 2 | XYZ | 5 | 16-Sep | |
| 3 | XYZ | 21 | 17-Sep | |
| 4 | ABC | 20 | 16-Sep | |
| 5 | ABC | 25 | 16-Sep | |
| 6 | ABC | 15 | 17-Sep | 2 |
| 7 | ABC | 10 | 17-Sep | 3 |
| 8 | ABC | 20 | 17-Sep | 1 |
Filter Date = 17 Sep
| Sr No | Customer Name | Turn Around Time | Date | Rank |
| 1 | XYZ | 10 | 16-Sep | |
| 2 | XYZ | 5 | 16-Sep | |
| 3 | XYZ | 21 | 17-Sep | 1 |
| 4 | ABC | 20 | 16-Sep | |
| 5 | ABC | 25 | 16-Sep | |
| 6 | ABC | 15 | 17-Sep | 3 |
| 7 | ABC | 10 | 17-Sep | 4 |
| 8 | ABC | 20 | 17-Sep | 2 |
Try to add an index column and create a measure as below.
RANK =
MINX (
FILTER (
SELECTCOLUMNS (
ALLSELECTED ( ParentData ),
"index", ParentData[Index],
"rank", RANKX ( ALLSELECTED ( ParentData ), ParentData[Turn Around Time],, DESC, DENSE )
),
[index] = MAX ( ParentData[Index] )
),
[rank]
)
Eric_Zhang
Hello, I am trying to solve nearly the same problem. Thanks for answer.
Why do we need index column any other unique column would do the trick? or is it need to be ordinal?
regards,This is a great post and has helped me with my pattern I am trying to solve.
However the only difference is that I need to have the ranking value reset for each Customer.
So if I have both Customer ABC and XYZ selected then each would be ranked Individually not all together. The RANK Values would be 1, 2 and 1,2 for both customers.
Any ideas how I can do this. I have been searching now and over a day.
Thanks in advance.