Dynamic ranking a column based on slicer created from master table

[Dharini]

Hi All,

I have a slicer called Region(from master table - Region) which has relation to Region field in Test table.

Region

Test1

Test2

Test3

Test4

I have to dynamically rank  the Test table based on value column depending on slicer selection - Region. I have data for test table as given below.

Region	ProjectId	Value
Test1	1	23
Test1	2	34
Test2	3	53
Test3	4	9
Test4	5	17
Test4	6	17

If Test1 is selected in Region slicer, then it should be displayed as below,

Region	ProjectId	Value	Rank
Test1	1	23	2
Test1	2	34	1

If Test 2 and Test 3 is selected in Region slicer,then it should be displayed as below ,

region	ProjectId	Value	Rank
Test2	3	53	1
Test3	4	9	2

If Test 2 and Test 4 is selected, then it should be displayed as below,

Region	ProjectId	Value	Rank
Test2	3	53	1
Test4	5	17	2
Test4	6	17	2

If All is selected in slicer,then it should be displayed as ,

Region	ProjectId	Value	Rank
Test1	1	23	3
Test1	2	34	2
Test2	3	53	1
Test3	4	9	5
Test4	5	17	4
Test4	6	17	4

i have created a rank measure as 

Rank = RANKX(ALLSELECTED('Test'),CALCULATE(SUM('Test'[Value]),ALLEXCEPT('Test',Region[REGION],'Test'[ProjectId])),,DESC,Dense).

This works fine when region slicer is created from test table.

But i wanted to dynamically rank the value column based on selection from region slicer(master).

Kindly do help.

Thanks

Dharini

[PattemManohar]

@Dharini Please try this as a "New Measure"

Test144 = RANKX(ALLEXCEPT(Test144DynamicRnk,Test144DynamicRnk[Region ]),CALCULATE(MAX(Test144DynamicRnk[Value]),ALLSELECTED(Test144DynamicRnk[Region ])),,DESC,Dense)

[Dharini]

@PattemManohar  Thanks for your quick reply.

This measure works fine when region slicer is created is created from Test table .

But when Region slicer is created from master table Region, Data is not filtered as given below in Test table

When Test1 is selected from region slicer, below data is displayed

Region	ProjectId	Value	Rank
Test1	1	23	3
Test1	2	34	2

 When Test1 and Test4 is selected in Region slicer , below data is displayed

Region	ProjectId	Value	Rank
Test1	1	23	3
Test1	2	34	2
Test4	5	17	4
Test4	6	17	4

[LivioLanzo · Accepted solution]

Hello @Dharini

I can suggest you build the below simple model:

Then this measure should do what you want. When you add the items to the table visual for Region, ProjectID and Value, choose do not summarize for all of them. Also notice that on the rows section of the table, Ive dropped the column of the dimension tables (projects, regions)

Ranking = 
IF(
    COUNTROWS( Data ) > 0,
    RANKX(
        CALCULATETABLE(
            SUMMARIZE( Data, Projects[ProjectId], Regions[Region ] ),
            ALLSELECTED()
        ),
        CALCULATE( MAX( Data[Value] ), ALLEXCEPT( Data, Regions[Region ], Projects[ProjectId] ) ),,DESC,Dense
    )
)

[Dharini]

Thankyou @LivioLanzo It works fine 🙂

[Anonymous]

HI- 

This is a very good video to help explain RankX 

RankX Video