Forum Discussion

Dharini's avatar
Dharini
Frequent Visitor
7 years ago
Solved

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 ran...
Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

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

     

LivioLanzo's avatar
LivioLanzo
Icon for Solution Sage rankSolution Sage
7 years ago

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's avatar
Dharini
Frequent Visitor
7 years ago

Thankyou LivioLanzo It works fine :)