cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculated Column for Hierarchical Filtering

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.
1 ACCEPTED SOLUTION
Super User

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.

7 REPLIES 7
Frequent Visitor

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.

Super User

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.

Frequent Visitor
 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.

Super User

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.

Frequent Visitor

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?

Super User

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.

Super User
`` 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.