Change Rank Dynamically by user selected Filter

[adnyesh]

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)

[Anonymous]

is it possible to create a slicer for this measure. For example to choose the Top 10?

[Sushmach109]

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?

[Eric_Zhang]

@adnyesh

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.

[adnyesh]

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

[Eric_Zhang · Accepted solution]

@adnyesh

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]
)

 

[Anonymous]

@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,

[jkenne01]

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.

[adnyesh]

@Eric_Zhang

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