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.
Sorry if this has been answered. I wasn't even sure how to search for this.
I have a dataset containing a list of incidents that have been resolved by a support team. The dataset includes various details about each support incident, such as Open Date, Close Date, Incident Owner, Incident Category, Escalation Information, etc.
I want to be able to measure the "breadth of knowledge" required of the Incident Owner based on how many different categories they had to resolve incidents against in the past 3 months (90 days). Then I want to plot that over time to see if the Incident Owner is working across more or less "Categories" over time.
For example:
Incident Number | Owner | Open Date | Close Date | Category | Escalated |
123 | James | 1/1/2020 | 1/3/2020 | Active Directory Account | False |
124 | Peter | 1/2/2020 | 1/24/2020 | Exchange Mailbox | True |
345 | James | 2/16/2020 | 3/7/2020 | Active Directory Account | False |
346 | Peter | 2/20/2020 | 2/24/2020 | SAP (Client) | False |
357 | James | 2/21/2020 | 2/22/2020 | Exchange Mailbox | False |
358 | Peter | 2/22/2020 | 3/1/2020 | SAP Access/Permissions | True |
I would get something like:
Date | Owner | Incidents in past 90 days | Categories in past 90 days |
3/1/2020 | James | 3 | 2 |
3/1/2020 | Peter | 3 | 3 |
Then I could group/summarize them to look at the whole organization, or look at individual teams inside that organization, and plot it over time to see the "breadth of knowledge" required.
Honestly, I don't even know what the secondary table/data should look like. Should it have every person and every date that they resolved a case? Should it just contain key dates, like month end? I'm mostly at a loss here.
I considered adding a new column on the base table that would filter the base table for incidents in the previous 90 days, with the same owner, then get a distinct count of those categories, but that seemed a bit heavy.
Thanks for any tips!
@tannerlindsay , you can try a formula like example formula and do count and distinctcount
Rolling 90 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-90,Day))
with date table
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
Appreciate your Kudos.
@amitchandak - Thanks. I will try that one out. However, I haven't been able to get a date table to work. I ran into two problems with it:
I'll check out your webinar, as the Time Intelligence is an area I would like to understand better.
Thanks!
@tannerlindsay , you can refer for userelation .userelation is way to same date table for both . in some cases you might need 2 date table. Likes opened and closed and different month like cohort
You can create hierarchy using a column in date table
https://stoneridgesoftware.com/creating-hierarchies-in-power-bi/
https://www.youtube.com/watch?v=x6vXVJZ_eTY
@amitchandak Thanks! I will check out those resource for my date table.
As to the original measure - your recommendation is REALLY close. It works well at telling me the number of distinct categories that were handled in the last 90 days. However, I'm perhaps not understanding how to do this over many users and then look at them as a group.
For example, I validated that it works by looking at User A. I manually filtered and checked, and they handled 14 different categories in the 90 days prior to Febuary. That shows up great on the visual. Then I looked at User B. They did 12 different categories, and the measure worked perfectly - as long as it was filtered to a single user. When I filtered it to both User A and User B, that is when I ran into problems. In that case the measure says 20. That is because between the two of them, they addressed 20 different categories. I want it to say 13, because User A was 14, User B was 12, so the average between them is 13 and I didn't see how it would separate the users.
For reference, here is the formula I have:
Categories in Past 90 Days = CALCULATE(DISTINCTCOUNTNOBLANK('Incident'[Category]),DATESINPERIOD('Date'[Date],MAX('Incident'[Created Date]),-90,DAY))
And if the answer is "go learn how to use CALCULATE", I'll accept that. 🙂
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |