Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table called Forms. Within that table, I have a field called forms, Total and YearMonth.
Forms | Total | YearMonth |
A | 10 | 202305 |
B | 20 | 202305 |
C | 25 | 202305 |
B | 20 | 202304 |
A | 45 | 202304 |
C | 5 | 202304 |
I would like my end result to be a table or some visualization where the user can see The top x forms by month.
For the month of April 202304 the top 3 forms were
A, B, then C
For the month of May 202305 the top 3 forms were
C, B, then A
How can I go about creating doing this in PowerBI? Thank you!
Hi @jenmar2023,
You don't need any DAX here.
All you need is:
1) Add a slicer.
2) Add a table visual.
3) Open the Filters pane and use the Top filter there for the [Total] column.
4) Enable sorting by [Total].
Hope this helps.
Best Regards,
Alexander
The user only sees it one month at a time though? They have to select it each month. Is there a way to show it in a table view all at once?
Okay, in this scenario you'll need some DAX.
You could create a calculated column like the one below.
In plain text:
Top3 Flag =
VAR CurrentMonth = [YearMonth]
VAR Top3 = CONCATENATEX ( TOPN ( 3, FILTER ( Data, [YearMonth] = CurrentMonth ), [Total] ), [Forms], "," )
RETURN CONTAINSSTRING ( Top3, [Forms] )
Once you have it, you should set it to True in the filter pane and sort your table by 2 columns holding the Shift key. Like on the screenshot below:
Best Regards,
Alexander
Didn't work. Can't display the visual when I add Top3 flag
Here's the file with the mock data where this solution works - https://www.dropbox.com/scl/fi/bemnu8v7a4ahcbpczew60/community-multiple-topx.pbix?rlkey=5ty9flldoh8t...
Please double check with what you do.
User | Count |
---|---|
52 | |
35 | |
19 | |
15 | |
14 |
User | Count |
---|---|
94 | |
72 | |
30 | |
22 | |
14 |