Forum Discussion
Dynamic ranking a column based on slicer created from master table
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 ) )
Community ChampionDharini Please try this as a "New Measure"
Test144 = RANKX(ALLEXCEPT(Test144DynamicRnk,Test144DynamicRnk[Region ]),CALCULATE(MAX(Test144DynamicRnk[Value]),ALLSELECTED(Test144DynamicRnk[Region ])),,DESC,Dense)
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
Solution Sage
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 ) )Thankyou LivioLanzo It works fine :)