Reply
Regular Visitor
Posts: 31
Registered: ‎08-30-2018

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,

 

Adam

 

Services_with_incident.JPGNumber of incidentperservice_last end dateperservice.JPG

Community Support Team
Posts: 889
Registered: ‎07-30-2018

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?

 

6.JPG

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

7.JPG

 

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

8.JPG

 

 

Best Regards,

Lin

 

 

 

 

 

 

 

 

Attachment
Regular Visitor
Posts: 31
Registered: ‎08-30-2018

Re: Number of Services with Incident

[ Edited ]

Hi @v-lili6-msft,

 

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

 

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

 

sum_asp_per_BU.JPGsum_asp_per_Service.JPG

 

Many-many thanks!


Adam 

Regular Visitor
Posts: 31
Registered: ‎08-30-2018

Re: Number of Services with Incident

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

Community Support Team
Posts: 889
Registered: ‎07-30-2018

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

14.JPG

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

 

 

Best Regards,

Lin

 

Regular Visitor
Posts: 31
Registered: ‎08-30-2018

Re: Number of Services with Incident

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:

 

New_Last_Incident.JPG

Community Support Team
Posts: 889
Registered: ‎07-30-2018

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