Forum Discussion
Dynamic ranking a column based on slicer created from master table
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
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 ) )
5 Replies
- PattemManohar
Community Champion
Dharini 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 ) )
