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.
Hi,
I have an "Incident" table looks like this, note that I created the column (Opened At Month) in Power Query editor so that it is easier for future aggretation based on month:
Incident:
"Opened At" | "Opened At (Month)" | "Incident ID" | "Met SLA" | "Age (Days)" | "Duration(Secs)" |
1-Sep-21 | Sep-21 | Inc090921 | Yes | 0.008 | 691.2 |
13-Sep-21 | Sep-21 | Inc130921 | Yes | 0.0009 | 77.76 |
2-Oct-21 | Oct-21 | Inc021021 | Yes | 0.0073 | 630.72 |
12-Oct-21 | Oct-21 | Inc121021 | No | 1 | 86400 |
2-Nov-21 | Nov-21 | Inc021121 | No | 0.9 | 77760 |
5-Nov-21 | Nov-21 | Inc051121 | No | 1 | 86400 |
11-Nov-21 | Nov-21 | Inc111121 | No | 1 | 86400 |
2-Dec-21 | Dec-21 | Inc021221 | Yes | 0.006 | 518.4 |
21-Dec-21 | Dec-21 | Inc211221 | No | 1 | 86400 |
8-Jan-22 | Jan-22 | Inc080122 | Yes | 0.0006 | 51.84 |
And based on this Incident table, I need to create an Incident Summary table that aggregates the number of incidents, and number of incidents that have met SLA etcs each month, it should look like this:
Incident Summary
"Opened At (Month)" | "Number of Incidents" | "Incidents that have Met SLA" | "Average Age (Days)" | "Duration(Secs) > 80000" |
Sep-21 | 2 | 2 | 0.00445 | 0 |
Oct-21 | 2 | 1 | 0.50365 | 1 |
Nov-21 | 3 | 0 | 0.9666666667 | 2 |
Dec-21 | 2 | 1 | 0.503 | 1 |
Jan-22 | 1 | 1 | 0.0006 | 0 |
I tried to use DAX to create a new table: Incident Summary = SUMMARIZE(Incidents, Incidents [Opened At (Month]), and created a relationship between Incident and Incident Summary. I managed to figure out the calculation of "the Number of Incident" column = CALCULATE (COUNTROWS(Incident), USERELATIONSHIP(Incident[Opened At (Month], 'Incident Summary'[Opened At (Month)]))
However, I am stuck with how to calculate number of Incidents that have met SLA (Met SLA = Yes), I tried to use this formula to create the "Met SLA" column but it does not give me the right number: Met SLA = CALCULATE(COUNT(Incident[Met SLA
]), FILTER (Incident, Incident[Met SLA]="Yes"))
It would be really appreciated if anyone can let me know how to summarise the following metrics in Incident Summary table:
1. Incidents that have Met SLA
2. Average Age (Days)
3. Duration(Secs) > 80000
I also tried Pivoting from Query Editor but it didn't help. Much appreciated if anyone can point me right direction on how to properly summarise a table in PowerBI, either using DAX or other methods, I am still relatively new to PowerBI. Thanks for very much for your help.
Solved! Go to Solution.
Hi,
Instead of using FILTER in CALCULATE you can do it like this:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
Proud to be a Super User!
Hi,
Instead of using FILTER in CALCULATE you can do it like this:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
Proud to be a Super User!
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |