Calculated Column for Hierarchical Filtering

[sbutton]

I'm attempting to calculate the mean time between events with my data.  One aspect of doing this is calculating the difference in time between one event and the next, and having that input as a calculated column.  

Uptime =

VAR next = MINX(FILTER(Report, Report[Location]=EARLIER(Report[Location]) && Report[Reported Date]>EARLIER(Report[Reported Date]) && OR(Report[Work Type]=="UM", Report[Work Type]=="CM")), Report[Reported Date])

RETURN IF([Work Type]="PM", 0, IF(ISBLANK(next),

    DATEDIFF([Reported Date], NOW(), SECOND),

    DATEDIFF([Reported Date],next, SECOND)))

The issue is that "Location" is a single column and I need this column to work with a hierarchy so that I can dive down in my report and look at the "mean time between events" as mean time in the main system, subsystem, etc. essentially diving through the hierarchy.

I think I would have to make some variable and reference that variable in my report but I'm not sure how to go about this.  This was the template I used - https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-Mean-Time-Before-Failure-MTBF-in-di...

If I try to drill down in my current configuration, the "Main System" doesn't show the correct mean time between events - i.e. it might show 200 days between events when there are 20 events in its subsystem occurring in 20 days.  

[sbutton]

Can you assist with what measure would create this?  I'm not sure how I can create a measure that calculates the difference between other rows and then averages it.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[sbutton]

Event	Location	System	Date Reported
Failure	Pump A	Boiler System	5/10/2022
Failure	Pump B	Boiler System	5/21/2022
Failure	Pump A	Boiler System	6/10/2022
Failure	Pump C	Boiler System	6/11/2022
Failure	Pump D	Condensate System	7/5/2022
Failure	Pump E	Condensate System	7/10/2022

Given a table of Dates, Locations, and System I would like to create a graph that displays the mean time between events (failures) by system.

I would then like to drill through / filter this graph by system to display mean times between failures by Location.  In this instance drilling down "Boiler System".

I'm not sure there's any easy way to accomplish this without requiring two calculated columns.

Time Between Failure (System)	Time Between Failure (Location)
0	0
11	0
20	31
1	0
0	0
0	5

I know how to create those calculated columns, but I'd like to be able to have those values change based on what is being filtered out.  I.E. the time between events would change if you're just looking at the time between events on "Pump A" vs. time between events on "Boiler System".  Since calculated columns are immutable I'm not sure how to achieve this.

[lbendlin · Accepted solution]

Thank you for the nice challenge.  Here is a universal measure that will work for all dimensions.

MTBF := 
var a = values(Failures[Date Reported])
var b = ADDCOLUMNS(a,"Previous",var t = [Date Reported] return CALCULATE(max(Failures[Date Reported]),Failures[Date Reported]<t))
var c = filter(b,not ISBLANK([Previous]))
return AVERAGEX(c,[Date Reported]-[Previous])

see attached.

[sbutton]

Very impressive!

I haven't yet tested it with every use case but this did the trick for me!

Based on your experience (since I believe with large data measures are usually avoided due to time requirements) is there a way to perform something like this in Power Query?  Or a more optimal way of doing this?

[lbendlin]

You can do this in Power Query or as a calculated column.  I don't think the performance will be much better, and you lose all the flexibility that you seem to want with your hierarchy etc. If you want flexibility you need to use measures.

[lbendlin]

 I need this column to work with a hierarchy so that I can dive down in my report and look at the "mean time between events" as mean time in the main system, subsystem, etc. essentially diving through the hierarchy.

Calculated columns are immutable. They cannot be influenced by user interaction.

You need to use measures.