Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I have a scoring table with multiple categories and I am trying to have visibility on top 2 failed categories per day, month, etc.
Name | Date | Category 1 | Category 2 | Category 3 | Category 4 | Category 5 |
ABC1 | 1/1/2023 | Passed | Passed | Failed | Passed | Failed |
DEF2 | 1/1/2023 | Failed | Passed | Failed | Failed | Failed |
ABC2 | 1/1/2023 | Failed | Failed | Failed | Failed | Failed |
DEF3 | 3/6/2023 | Passed | Passed | Failed | Passed | Failed |
ABC3 | 3/18/2023 | Failed | Failed | Passed | Failed | Failed |
DEF4 | 3/18/2023 | Failed | Failed | Failed | Passed | Failed |
ABC4 | 5/5/2023 | Failed | Passed | Failed | Failed | Failed |
DEF5 | 5/20/2023 | Passed | Passed | Failed | Passed | Failed |
ABC5 | 7/18/2023 | Failed | Failed | Passed | Failed | Failed |
DEF6 | 9/4/2023 | Passed | Passed | Failed | Passed | Failed |
The only idea I could think of, is to do measures for each categories and sum the daily counts, then compare in another measure. The list I have has over 10 categories, and I am wondering if there is a way to do it in one measure? I am hoping to be able to have a table or chart by month to show the top 2 failing each month for example.
Hi, @Beho
You can try the following methods. Check the first two columns-Transform-Unpivot Other column:
Date table:
Measure:
Count = CALCULATE(COUNT('Table'[Name]),FILTER(ALLEXCEPT('Table','Table'[Category]),[Value]="Failed"&&MONTH([Date])=SELECTEDVALUE('Date'[Month])))
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-zhangti, My issue is the report has a lot of visuals already set up, once I unpivot, the duplicates mess up the numbers.
Can I create another table and somehow have the dates, names, concatenated column, and then split in that table instead. This way it doesn't touch the original table?
Hi, @Beho
Of course. You can copy and paste a copy of your original table in power query. Do the Unpivot operation in the copied data table.
Best Regards
Quick question, if I copy the table, does the new table stay updated? I mean if the original table gets new rows or modified rows, does the copy update automatically? Thanks
@Beho , If this your source data and the category is on the column, then unpivot it
Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g
Now you can have measure
M1= countrows(Table)
You can use TopN
Calculate([M1], keepfilters(topn(2,allselected(Table[Category]), [M1], desc)))
or window
Calculate([M1], keepfilters(window(1,abs,2,abs,allselected(Table[Category]),orderby( [M1], desc) ))
refer
Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f
Learn Power BI: Dynamic TOPN using TOPN/Window and Numeric parameter: https://youtu.be/vm2mdEioQPQ
TOPN: https://youtu.be/QIVEFp-QiOk
TOPN with Numeric Parameter -https://youtu.be/cN8AO3_vmlY?t=26448
You might have to switch measures based on view by on selection
IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM
I tried unpivoting some categories to test, it created a lot of duplicates to my data and it updated all the visuals in my report to the incorrect counts. I will go through the videos, but if it will always create dups, then it would not work.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
88 | |
82 | |
65 | |
64 | |
58 |
User | Count |
---|---|
171 | |
115 | |
109 | |
74 | |
69 |