Forum Discussion
Change Rank Dynamically by user selected Filter
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)
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] )
8 Replies
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 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
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] )
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?
