Measure "breadth of knowledge" based on incident information

[tannerlindsay]

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!

[amitchandak]

@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.

[tannerlindsay]

@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:

• My base fact table contains two dates - Open Date and Close Date. I need to use these different dates, depending on the visualization. For example to see incoming volume, I go by Open Date. To see Resolution Time, I go by Closed Date. However, I was unable to create a relationship between my date table and *both* dates in the fact table. I was able to mostly deal with this with the USERELATIONSHIP function, but it was annoying, and then I ran into the next problem.
• When using my own date table, I was unable to get PowerBI to handle a heirarchy as continuous at all levels. It would allow the axis to be continuous at the Year level, but as soon as I went to Quarter, it switches to categorical - making the visualization less useful and unable to do trends or forecasts. This is mentioned in the comments of a SQLBI post, and they suggested there would be a detailed blog forthcoming, but it hasn't forthcome yet.

I'll check out your webinar, as the Time Intelligence is an area I would like to understand better.

Thanks!

[amitchandak]

@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 

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

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

[tannerlindsay]

@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. 🙂