cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Measure "breadth of knowledge" based on incident information

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 NumberOwnerOpen DateClose DateCategoryEscalated
123James1/1/20201/3/2020Active Directory AccountFalse
124Peter1/2/20201/24/2020Exchange MailboxTrue
345James2/16/20203/7/2020Active Directory AccountFalse
346Peter2/20/20202/24/2020SAP (Client)False
357James2/21/20202/22/2020Exchange MailboxFalse
358Peter2/22/20203/1/2020SAP Access/PermissionsTrue

 

I would get something like:

DateOwnerIncidents in past 90 daysCategories in past 90 days
3/1/2020James32
3/1/2020Peter33

 

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!

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Measure "breadth of knowledge" based on incident information

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Measure "breadth of knowledge" based on incident information

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

Highlighted
Super User IV
Super User IV

Re: Measure "breadth of knowledge" based on incident information

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Measure "breadth of knowledge" based on incident information

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

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors