Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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-different-period-of/td-p/895369
 
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.  
  • lbendlin's avatar
    lbendlin
    4 years ago

    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

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago
     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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      lbendlin
      Super User
      4 years ago

      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-p/963216

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago
        EventLocationSystemDate Reported
        FailurePump ABoiler System5/10/2022
        FailurePump BBoiler System5/21/2022
        FailurePump ABoiler System6/10/2022
        FailurePump CBoiler System6/11/2022
        FailurePump DCondensate System7/5/2022
        FailurePump ECondensate System7/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)
        00
        110
        2031
        10
        00
        05

         

        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.