Number of Services with Incident

[Anonymous]

Hey guys,

I would kindly ask for your help concerning 3 separate formulas. - I am totally new to Power BI and only know Excel...

I also uploaded the power bi file to one drive, here is the link:

https://1drv.ms/u/s!Ah2VGOQRBzRVhXZ4BCAwMfZYLIbI

1, I would be interested to know, the number of services with incident per BU - see pic 1

2, I would be interested to know the number of incidents per service (if it has no end date, still should count) - see pic 2

3, I would like to see the last Incident end Date per service.

Any help is greatly appreciated!!!

Many thanks,

Adam

[v-lili6-msft]

HI, @Anonymous

After my test on your sample pbix file.

1.you may try this formula 

NEW Number of Services with Incident = CALCULATE(COUNTA('Incident Report'[LookupToServiceDashboardID]),
FILTER(ServiceDashboard,
ServiceDashboard[BU] = "REF" ||
ServiceDashboard[BU] = "RET" ||
ServiceDashboard[BU] = "GL" ||
ServiceDashboard[BU] = "PSS" ||
ServiceDashboard[BU] = "CST" ||
ServiceDashboard[BU] = "FIN" ||
ServiceDashboard[BU] = "ACC" ||
ServiceDashboard[BU] = "MAR" ||
ServiceDashboard[BU] = "SAL" ||
ServiceDashboard[BU] = "SMT"))+0

But why ACC is 0 REF is 1, What did I misunderstand?

2. you could try this formula 

NEW Number of Incidents = CALCULATE(COUNTA('Incident Report'[Title]),
FILTER(ServiceDashboard,
ServiceDashboard[BU] = "REF" ||
ServiceDashboard[BU] = "RET" ||
ServiceDashboard[BU] = "GL" ||
ServiceDashboard[BU] = "PSS" ||
ServiceDashboard[BU] = "CST" ||
ServiceDashboard[BU] = "FIN" ||
ServiceDashboard[BU] = "ACC" ||
ServiceDashboard[BU] = "MAR" ||
ServiceDashboard[BU] = "SAL" ||
ServiceDashboard[BU] = "SMT"))+0

3. I found that you had created a relationship between ServiceDashboard and CALENDAR service created

by Incident Created column and date column.

But Incident Created column is datetime column with different time and date column a datetime column with only 12:00:00 AM

So they just have the same date but different time, they don't match.

Therefore, you need to add a new date column for Service Created.

New Service Created = ServiceDashboard[Service Created].[Date]

then use this column to create a relationship between ServiceDashboard and CALENDAR service created.

And I also find that your measure

Last Incident = CALCULATE(MAX('CALENDAR'[Date]))

CALENDAR has no relationship with ServiceDashboard, so the result will always be 12/31/2018.

I would like to see the last Incident end Date per service.

Do want to see the last incident end date per service or the last incident create date per service?

Is this formula?

new Last Incident = CALCULATE(MAX('Incident Report'[Incident End Date]))

Best Regards,

Lin

[Anonymous]

hi @v-lili6-msft,

for your question: "what did I misundarstand": REF should be "1" instead of "2" as the same service "afternoon" is counted twice. The 2 different incidents belong to the same service (afternoon).

[Anonymous]

Hi @v-lili6-msft,

thank you again so much for your help! You really cannot imagine, how much you helped me already 🙂

All the answers are working perfectly!

Would it be just possible to extend the 3rd one:

- if the incident has an end date, then it should show the last end date (as it is now). However it can happen that certain incidents have only a start date (not yet closed) in that case the last "start date" should be shown.

https://1drv.ms/u/s!Ah2VGOQRBzRVhho-2YGQo3iarahk

-and maybe also adding separately per BU: amount of Incidents with ASPStatus ID 1 & 2 -see pic

-and the same separately per Service: amount of Incidesnts with ASP Status ID 1 & 2 -see pic

Many-many thanks!

Adam 

[v-lili6-msft]

hi, @Anonymous

After my test, you could use DISTINCTCOUNT Function to add a NEW Number of Services with Incident 2 measure

NEW Number of Services with Incident 2 = var _exsitID=CALCULATE(COUNTA('Incident Report'[LookupToServiceDashboardID])) RETURN
IF(ISBLANK(_exsitID)=FALSE(),CALCULATE(DISTINCTCOUNT(ServiceDashboard[Title]),
FILTER(ServiceDashboard,
ServiceDashboard[BU] = "REF" ||
ServiceDashboard[BU] = "RET" ||
ServiceDashboard[BU] = "GL" ||
ServiceDashboard[BU] = "PSS" ||
ServiceDashboard[BU] = "CST" ||
ServiceDashboard[BU] = "FIN" ||
ServiceDashboard[BU] = "ACC" ||
ServiceDashboard[BU] = "MAR" ||
ServiceDashboard[BU] = "SAL" ||
ServiceDashboard[BU] = "SMT"),FILTER('Incident Report',ISBLANK(_exsitID)=FALSE())))+0

 and If certain incidents have only a start date (not yet closed)

You could use this formula

new last incident = IF(ISBLANK([Last Incident])=FALSE(),[Last Incident],CALCULATE(MAX('Incident Report'[Incident Start Date]) ))

By the way, if there two or more titles 

assume

In this case, last incident is 11/13/2018 or 11/12/2018.

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft,

many thanks for your fast reply! The first one is working properly! For the second one, would it be possible to add if the service has 2 different incidents: 1 with and 1 without and End Date. (The 1 without end date starts later, than the end date of the other incident, then the start date should be taken) - more visible on the pic:

[v-lili6-msft]

hi, @Anonymous

You may try this formula as below:

Measure = IF( CALCULATE(MAX('Incident Report'[Incident End Date]))>CALCULATE(MAX('Incident Report'[Incident Start Date])),CALCULATE(MAX('Incident Report'[Incident End Date])),CALCULATE(MAX('Incident Report'[Incident Start Date])))

Best Regards,

Lin

[Anonymous]

hi @v-lili6-msft Lin,

many thanks again for your support, works perfectly! You really cannot imagine you are helping me!!!

Could I have 2 very last question?

1, Would it be possible to calculate also separately for the Business Units, how much services they had with incidents, that had ASP Status ID1 and in a different one, how much had ASP Status ID2 (measure). As of now, you helped me to calculate "Number of Services with Incident 2" and this would be like an additional split for ASP ID 1 and 2. - see pic

2, Would it be possible to calcualte also separately for the Services separately, how much incidents they had with ASP Status ID1 and with ASP Status ID2. As of now, you helped me to calculate "New Number of  Incidents" per Service, it would be again like a split. - see pic

Thank you already in advance,

Many thanks,

Adam

[v-lili6-msft]

HI, @Anonymous

I'm a little confused about it, what is your expected output for it.

Could you please explain it with the data and expected output, I think you could add two more measure, one adding conditional

ASP Status = "1" and another adding conditional ASP Status = "2"  in the formula. This is the simplest method.

Best Regards,

Lin

[Anonymous]

Hi @v-lili6-msft,

thank you for your answer. I created 2 new screenshots with data, but try to reformulate as well 🙂

For point Nr.1: There are BUs, for example PSS that has multiple Services, out of them a few Services can have incidents (this is now New Number of Services with Incident 2 on the pic). The incidents can have 2 categories, major and ciritcial (ASP StatusID1 and 2). I would be interested to know per BU how many incidents they had with ASP Status ID 1 (measure 1) and with ASP Status 2 (measure 2). For example for PSS it would mean 2 with ID1 and 1 with ID2.

For point Nr.2: Same logic, there are Services with major and critical incidents and currently I can see the number of total incidents (it is Number of Incidents on the pic). I would be also interested to have separately, how many major (ASP Status ID1) and critical (ASP Status 2) incidents occured per Service, not only the total. For example on the screenshot service "excel" has a total of 2 incidents, 1 critical and 1 major, so I would like to see 1 and 1. (guess also 2 different measures?)

Many thanks!

Bests,

Adam

[v-lili6-msft · Accepted solution]

hi, @Anonymous

For point Nr.1: you could try these two measure

Measure 1 = CALCULATE(COUNTA('Incident Report'[ASPStatusID]),FILTER('Incident Report','Incident Report'[ASPStatusID]=1))


Measure 2 = CALCULATE(COUNTA('Incident Report'[ASPStatusID]),FILTER('Incident Report','Incident Report'[ASPStatusID]=2))

For point Nr.2: you could also try these two measure

Measure 3 = CALCULATE(COUNTA('Incident Report'[ASPStatusID]),FILTER('Incident Report','Incident Report'[ASPStatusID]=1))



Measure 4 = CALCULATE(COUNTA('Incident Report'[ASPStatusID]),FILTER('Incident Report','Incident Report'[ASPStatusID]=2))

IF you want "0" instead of blank value in the visual, you could add "+0" at the end of each formula.

Best Regards,

Lin