Forum Discussion

smpa01's avatar
smpa01
Community 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
Super User
4 years ago

smpa01 Thanks for explaining. From a discovery study point of view is fine but I will probably never use this for production. And as a matter of fact I never came across such a use case but good to know how you are using it.

 

Cheers,

P

smpa01's avatar
smpa01
Community Champion
4 years ago

parry2k  just so you know, from a production standpoint, it is fine (had no issues whatsover- in my case SSAS dbs feed both dataflow and dataset) and dataset refresh are blazing fast for the SSAS dbs (faster than SQL refresh).

Also, we paid to have a Premium capacity workspace and why not take full advantage of that?