Forum Discussion

abhiram342's avatar
abhiram342
Microsoft Employee
6 years ago

RankX function across multiple Dimension Tables

Hi All,   I have two dimension tables and one fact table . I have Product Table (Dimension) , Customer Table ( Dimesnion)  and Sales table (Fact). For Each Product, I would like display Top 3 Custo...
DAX
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi abhiram342 ,

 

You can use this measure

 

Top 3 = 
SWITCH(
    TRUE(),
    ISINSCOPE(Customer[Name]), RANKX(ALL('Customer'[Name]),[Total Sales]),
    ISINSCOPE('Item'[Brand]), RANKX(ALL('Item'[Brand]), [Total Sales])
)

 

Create a Visual Filter where Top3 is less than 3.

 

 

 

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

  • abhiram342's avatar
    abhiram342
    Microsoft Employee
    6 years ago

    Thanks Harsh ! I have replaced with actual tables and the DAX code is runnig for more than 10 mins . Can you please suggest if we have any alternatives

     

    Thanks,

    Abhiram