Forum Discussion

TFRec's avatar
TFRec
Frequent Visitor
2 years ago
Solved

Nested (3 levels) TOPN using Power BI DAX not working for categories but working for other

I need to provide a matrix wherein I show the top 3 product codes, those codes' top 3 countries of origin, and those origin countries' top 3 destination countries. I tried to use the below formu...
Need Help
qqqqqwwwweeerrr's avatar
qqqqqwwwweeerrr
Icon for Solution Sage rankSolution Sage
2 years ago

Hi   TFRec 

lbendlin already provided a very good approach, but still, if you seeking another way of doing that you can check this video this is not exact but with some modification this might help you to attain solution: https://youtu.be/cs2AwJljBrQ?si=yQR7T5WZZMfTaSDh

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

Regards

TFRec's avatar
TFRec
Frequent Visitor
2 years ago

Hello qqqqqwwwweeerrr ,

Thank you so much for this. I just had to modify it a little bit and it works perfectly. I was really overcomplicating it with my use of nested TOPNs with KEEPFILTERS.

Top 3 multiple test = 
VAR RankCode = RANKX(
    ALLSELECTED(Table[Product_code]),[Sum Euros All Time],,DESC,Dense
)

VAR RankOrigin = 
    RANKX(
        ALLSELECTED(Table[Origin_COuntry]),[Sum Euros All Time],,DESC,Dense
    )

VAR RankDestination =
    RANKX(
        ALLSELECTED(Table[Destination_Country]),[Sum Euros All Time],,DESC,Dense
    )

VAR RankAll =
SWITCH(
    TRUE,
    ISINSCOPE(Table[Destination_Country]),RankDestination,
    ISINSCOPE(Table[Origin_Country]),RankOrigin,
    ISINSCOPE(Table[Product_code]),RankCode  
)

RETURN
IF(
    RankAll <= 3, [SumEuros],BLANK()
)