cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smpa01
Super User
Super User

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 to write a DAX table expression with TOPN that returns a TOPN(3) SUBCAT for each CAT.  I am not looking for a RANKX expression.

Please allow me to demonstrate that with an example. My table is following (pbix is attached)

which has 3 CATS -> CAT1, CAT2, CAT3

and each CAT has -> 5 subCATS

 

| CAT  | subCAT       | Value |
|------|--------------|-------|
| CAT1 | electrical   | 3319  |
| CAT1 | plumbing     | 3451  |
| CAT1 | carpentry    | 9550  |
| CAT1 | hvac         | 1665  |
| CAT1 | fls          | 7156  |
| CAT1 | electrical   | 1000  |
| CAT1 | plumbing     | 8057  |
| CAT1 | carpentry    | 5690  |
| CAT1 | hvac         | 1597  |
| CAT1 | fls          | 8749  |
| CAT2 | locks        | 1007  |
| CAT2 | roadworks    | 8967  |
| CAT2 | hvac         | 3530  |
| CAT2 | fls          | 8027  |
| CAT2 | electrical   | 5519  |
| CAT2 | locks        | 7830  |
| CAT2 | roadworks    | 7009  |
| CAT2 | hvac         | 3346  |
| CAT2 | fls          | 3697  |
| CAT2 | electrical   | 3890  |
| CAT3 | admin        | 7059  |
| CAT3 | depriciation | 7707  |
| CAT3 | interest     | 6752  |
| CAT3 | tax          | 5867  |
| CAT3 | COGS         | 5641  |
| CAT3 | admin        | 6133  |
| CAT3 | depriciation | 5288  |
| CAT3 | interest     | 5882  |
| CAT3 | tax          | 3073  |
| CAT3 | COGS         | 3461  |

 

If I pivot

 

tbl=GROUPBY(tbl,tbl[CAT],tbl[subCAT],"Value",SUMX(CURRENTGROUP(),tbl[Value]))

 

the above data, it looks like this

 

| CAT  | subCAT       | Value |
|------|--------------|-------|
| CAT1 | fls          | 15905 |
| CAT1 | carpentry    | 15240 |
| CAT1 | plumbing     | 11508 |
| CAT1 | electrical   | 4319  |
| CAT1 | hvac         | 3262  |
| CAT2 | roadworks    | 15976 |
| CAT2 | fls          | 11724 |
| CAT2 | electrical   | 9409  |
| CAT2 | locks        | 8837  |
| CAT2 | hvac         | 6876  |
| CAT3 | admin        | 13192 |
| CAT3 | depriciation | 12995 |
| CAT3 | interest     | 12634 |
| CAT3 | COGS         | 9102  |
| CAT3 | tax          | 8940  |

 

Is there a way to write a TOPN table expression that returns the following (i.e. TOPN 3 subCAT for each CAT by Value)

 

| CAT  | subCAT       | Value |
|------|--------------|-------|
| CAT1 | fls          | 15905 |
| CAT1 | carpentry    | 15240 |
| CAT1 | plumbing     | 11508 |
| CAT2 | roadworks    | 15976 |
| CAT2 | fls          | 11724 |
| CAT2 | electrical   | 9409  |
| CAT3 | admin        | 13192 |
| CAT3 | depriciation | 12995 |
| CAT3 | interest     | 12634 |

 

I can write an expression like this

 

Table =
VAR _base =
    TOPN (
        18,
        FILTER ( ALL ( tbl ), tbl[CAT] = CALCULATE ( MAX ( tbl[CAT] ) ) ),
        CALCULATE ( SUM ( tbl[Value] ), ALLEXCEPT ( tbl, tbl[CAT], tbl[subCAT] ) ), DESC
    )
RETURN
    GROUPBY (
        _base,
        tbl[CAT],
        tbl[subCAT],
        "Value", SUMX ( CURRENTGROUP (), tbl[Value] )
    )

 

But instead of grouping later, I want to do that at the beginning and how to pass on that table to TOPN to do the job?

Pbix is attached

@parry2k  @CNENFRNL 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

 

AlexisOlson_0-1642090312102.png

View solution in original post

14 REPLIES 14
CNENFRNL
Super User
Super User

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

CNENFRNL_0-1642111058587.png

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


parry2k
Super User
Super User

@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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Thanks, @parry2k  🙏





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


parry2k
Super User
Super User

@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






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


parry2k
Super User
Super User

@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).






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


parry2k
Super User
Super User

@smpa01 gotcha, different use case how you are using it but good for you cheers!!

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





AlexisOlson
Super User
Super User

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

 

AlexisOlson_0-1642090312102.png

@AlexisOlson  this is exactly what I was looking for and I don't mind taking the longer one. This is simply crazy awesome !!!!

 

Thanks !!!

 

@parry2k DAX is super awesome, to say the least, and other than simply using DAX only for viz, I use it as a fully-fledged query language for my dataset published in the workspace. So for that, I don't need a measure.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


parry2k
Super User
Super User

@smpa01 sorry about that. hmm I'm actually not clear what you are looking for. Sorry.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User
Super User

@smpa01 try this measure

 

Top 3 = 
CALCULATE ( 
    [Sum], 
    KEEPFILTERS ( 
        TOPN ( 
            3, 
            FILTER ( 
                ALLSELECTED ( 
                    tbl[CAT], 
                    tbl[SubCat] ), 
                    tbl[CAT] = MAX ( tbl[CAT] ) 
                ),
                [Sum], 
                DESC 
            ) 
        )  
    )

 

parry2k_0-1642089843315.png

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Hi @parry2k  thanks for this and I was not actually looking for a measure. I got this measure by myself.

 

I was simply trying to solve the mystery of how to generate a DAX derived table as per my req.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!