Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
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
Community Champion
Community Champion

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 still way 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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
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).



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
parry2k
Super User
Super User

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

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.