Forum Discussion

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago
Solved

DAX Table expression to return TOPN by partition

AlexisOlson  I might have brought this one up in a different post with you but never really had a chance to properly ask the question. This post might get long with follow-up questions My goal is t...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    How about this?

    GENERATE (
    VALUES ( tbl[CAT] ),
    CALCULATETABLE (
        TOPN (
            3,
            ADDCOLUMNS (
                SUMMARIZE ( tbl, tbl[subCAT] ),
                "@Sum", CALCULATE ( SUM ( tbl[Value] ) )
            ),
            [@Sum]
        )
    )
)

    Or not best practice but shorter,

    GENERATE (
    VALUES ( tbl[CAT] ),
    CALCULATETABLE (
        TOPN (
            3,
            SUMMARIZE ( tbl, tbl[subCAT], "@Sum", SUM ( tbl[Value] ) ),
            [@Sum]
         )
    )
)

     

parry2k's avatar
parry2k
Icon for Super User rankSuper User
4 years ago

smpa01 yes, I understand, it is totally fine and one should take advantage but it depends. In this example, you have fixed Top 3 but if someone has to make it dynamically based on selection this will fail, so it all boils down to what is the business requirement, and what they want. I really appreciate your insight, if (i hope not) ever come to such a scenario I will this option in mind but until now working with 50+ customers (mid to large), not come across such a scenario but always there is an opportunity.

 

Cheers,

P

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Thanks, parry2k  🙏