cancel
Showing results for
Did you mean:
Regular Visitor

## Number of Services with Incident

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,

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Number of Services with Incident

hi, @Tenkes77

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 REPLIES 10
Community Support Team

## Re: Number of Services with Incident

HI, @Tenkes77

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Number of Services with Incident

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!

Regular Visitor

## Re: Number of Services with Incident

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

Community Support Team

## Re: Number of Services with Incident

hi, @Tenkes77

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Number of Services with Incident

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:

Highlighted
Community Support Team

## Re: Number of Services with Incident

hi, @Tenkes77

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Number of Services with Incident

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

Many thanks,

Community Support Team

## Re: Number of Services with Incident

HI, @Tenkes77

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Number of Services with Incident

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,