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 On another note, very rarely I need to use DAX query to create tables in the model (maybe 1 or 2 times max, very rare).

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

    parry2k we have sales, engineering, finance, lease ...dataset published in the workspace. Imagine at least 20 datasets (feeding 20 reports) (which are essentially SSAS db) for each of them.

     

    Since the SSAS dbs are available to me and I need to make a study that involves SALES+FINANCE instead of reinventing the wheel (going at the backend SQL server, fetching untransformed tables, applying SQL/PQ to transform, building a model again), I can simply use SSAS query on the clean SSAS tables (dataset/dataflow works in both) to fetch tables (transform/join/whatever) and build new models. I can save so much time by doing this.

     

    If you have a use case like this parry2k  you need to unleash DAX and it is capable of more than we normally think it is.