Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
@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
Solved! Go to Solution.
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]
)
)
)
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
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 😀✌
@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 🙏
@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?
@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.
@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.
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 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.
@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.
@smpa01 try this measure
Top 3 =
CALCULATE (
[Sum],
KEEPFILTERS (
TOPN (
3,
FILTER (
ALLSELECTED (
tbl[CAT],
tbl[SubCat] ),
tbl[CAT] = MAX ( tbl[CAT] )
),
[Sum],
DESC
)
)
)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |