Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am using a RANKX function in which i am passing the following statement
Rank = RANKX(FILTER(ParentData,ParentData[Customer]="XYZ"),VALUE(ParentData[TurnAroundTime]),,ASC,Skip)
This is providing rank according to all the elements with Customer= XYZ.
Now this is working fine since the value No is static and rank column is created when the column is created.
But i want the user to select the customer from the report filter. The value will reflect in the RANKX query on the run time and ranking will be done according to the filtered rows.
e.g.
Rank = RANKX(FILTER(ParentData,ParentData[Customer]="{Value from selected Filter}"),VALUE(ParentData[TurnAroundTime]),,ASC,Skip)
Solved! Go to Solution.
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] )
is it possible to create a slicer for this measure. For example to choose the Top 10?
Hi Team,
For Large data, this measure is not working .
For small data it is working.
May I know any similar formula for column instead of measure (OR) any similar formula that can be used in query editor?
I don' quite get your scenario, maybe you can try a dax as below, it ranks customer individually.
Rank = RANKX ( FILTER ( ParentData, EARLIER ( ParentData[Customer] ) = ParentData[Customer] ), VALUE ( ParentData[TurnAroundTime] ), , ASC, SKIP )
If it is not your case, please be more specific by posting sample data and expected output.
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.
Thanks for the reply, to elaborate the requirement i would give the below example of how the ranking needs to be done. The ranking would change according to the filters selected by the user at the runtime.
For e.g.
No Filter Selected
Customer Name Session TAT Date Rank
XYZ 1 20 11Sep 4
XYZ 2 15 11Sep 5
XYZ 3 25 12Sep 1
ABC 4 10 11Sep 6
ABC 5 15 11Sep 5
ABC 6 20 12Sep 4
ABC 7 23 12Sep 2
ABC 8 22 12Sep 3
Filter :Customer Name = ABC
Customer Name Session TAT Date Rank
XYZ 1 20 11Sep
XYZ 2 15 11Sep
XYZ 3 25 12Sep
ABC 4 10 11Sep 5
ABC 5 15 11Sep 4
ABC 6 20 12Sep 3
ABC 7 23 12Sep 1
ABC 8 22 12Sep 2
Filter :Customer Name = ABC & Date = 11 Sep
Customer Name Session TAT Date Rank
XYZ 1 20 11Sep
XYZ 2 15 11Sep
XYZ 3 25 12Sep
ABC 4 10 11Sep 2
ABC 5 15 11Sep 1
ABC 6 20 12Sep
ABC 7 23 12Sep
ABC 8 22 12Sep
Filter :Date = 12 Sep
Customer Name Session TAT Date Rank
XYZ 1 20 11Sep
XYZ 2 15 11Sep
XYZ 3 25 12Sep 1
ABC 4 10 11Sep
ABC 5 15 11Sep
ABC 6 20 12Sep 4
ABC 7 23 12Sep 2
ABC 8 22 12Sep 3
Hope it clarifies
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |