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]
         )
    )
)

     

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
4 years ago

Hi, pal! Seems I'm late, elegant DAX solutions are in place; thus I can offer nothing but an M solution then.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZC5DsIwEET/JXUKX3HsEqWgpIAuSmEcAxaOEznm+nvsKMDS7GrmSTujbdui2RxwURYnN6eJK4mqoiu/tlZhMj6G1wIJQxBO7jYcrT9nhiskIDPO6BisVi4JRrGE8HJXOi1KOPnYJOkwqv4xhuvapOYQrgVxTRi0/3IkQxJCN+rlmBC0hv6az8Uvgiat+sH6HJLqEgh6M6UIq6IdF06krCC3Pppg5rgwThlkzW67z9Uw+jsZ1TMXk/mj3Rs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CAT = _t, subCAT = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CAT", type text}, {"subCAT", type text}, {"Value", Int64.Type}}),

    Top3 = Table.ExpandTableColumn(Table.Group(#"Changed Type", {"CAT"}, {"all", each Table.MaxN(_, each [Value], 3)}), "all", {"subCAT", "Value"}, {"subCAT", "Value"})
in
    Top3

 

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

CNENFRNL late to the party but I promise to always invite you to the future crazy DAX games 😀✌