Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

MTBF implementation for Service Now

Hi All,

 

I am stuck in a situation where I have service Now (SNOW) data for P1,P2 and want to calculate MTBF based upon the assignment group.But confused about the situation when we have only one incidence for a particular assignment group.I want to calculate the days ;if there is no other incidence then it should give the difference of today date and creation date.So far I have written this DAX code to calculate this logic.

 

MTBFCount = If((MAX([Created]) && max([Priority])="1 - Critical" ||Max([Priority])="2 - High" ), DateDiff(MAX([2nd_highestvalue]),MAX([Created]),DAY),0)

 

2nd_highestvalue = CALCULATE(MAX('INC Overview'[Created]),FILTER('INC Overview', EARLIER('INC Overview'[Priority])='INC Overview'[Priority] && EARLIER('INC Overview'[Created])>'INC Overview'[Created] && EARLIER('INC Overview'[Assignment group])='INC Overview'[Assignment group]))

 

 

Note:: I have also using another calculated column '2nd_highestvalue' which is giving me the second latest creation date of that priority incidence and for that assignment group.

'INC Overview' is the table.

So can u please guide me to embed  the logic (if there is no other incidence then it should give the difference of today date and creation date)

Also how I can take the average  days of that;again classifoication should be the priority and assignment group.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi,

 

In my case the above solution did not work.I tried with another solution.

 

I created a Row count column as below which groupe the data based upon the Assignment group and priority and gives the count of this .

 

Row_counts = CALCULATE(COUNTROWS('INC Overview'),ALLEXCEPT('INC Overview','INC Overview'[Priority],'INC Overview'[Assignment group]))

 

then I created another column let say Avg_MTBF which calculated the time between two failure of incidences and also cover those cases where we recieved only one incidence (in that case it will give the difference between today date and the day when it was occurred.)

 

Avg_MTBF = If(([Row_counts]>1 && [2nd_highestvalue] <>BLANK()), DateDiff([2nd_highestvalue],[Created],DAY),
IF([Row_counts]=1 && [2nd_highestvalue] =BLANK(),ABS(DateDiff(TODAY(),[Created],DAY)),0))

 

 

View solution in original post

6 REPLIES 6
v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi,

 

In my case the above solution did not work.I tried with another solution.

 

I created a Row count column as below which groupe the data based upon the Assignment group and priority and gives the count of this .

 

Row_counts = CALCULATE(COUNTROWS('INC Overview'),ALLEXCEPT('INC Overview','INC Overview'[Priority],'INC Overview'[Assignment group]))

 

then I created another column let say Avg_MTBF which calculated the time between two failure of incidences and also cover those cases where we recieved only one incidence (in that case it will give the difference between today date and the day when it was occurred.)

 

Avg_MTBF = If(([Row_counts]>1 && [2nd_highestvalue] <>BLANK()), DateDiff([2nd_highestvalue],[Created],DAY),
IF([Row_counts]=1 && [2nd_highestvalue] =BLANK(),ABS(DateDiff(TODAY(),[Created],DAY)),0))

 

 

Thank you very much for sharing with the community. @Anonymous

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Seward12533
Solution Sage
Solution Sage

Assuming second higher last value is blank if there is only one

MTBFCount = VAR CompareDate = IF([2nd_highestvalue],max([2nd_highestvalue]),TODAY()) RETURN
If((MAX([Created]) && max([Priority])="1 - Critical" ||Max([Priority])="2 - High" ), DateDiff(CompareDate,MAX([Created]),DAY),0)
Anonymous
Not applicable

Hi,

 

Really thanks for your prompt help.

However when I am using this as a cal measure then its giving me the below errorcalc_measure errorcalc_measure errorand when I am trying to create a calc column then it is calculating all the values zero;which make no sense to me.

 

Please can you suggest what could be the best way to handle this.

 

Sorry Saines your second highest measure returned a distinct value. Just wrap the test for the second highest value in a max or any other aggregate function in both the terms of the VAR MAX([2nd_highestvalue]),

The reason it didn’t work as a calc column is that the filter context is limited to that row unless you use EARLIER or something to expand it so there is never a second higher at value for a single row.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors