Calculate difference between rows based on slicer selection

[Sachintha]

I have two tables as follows.

First, a list of alamrs occurred on a bunch of machines, ordered by time stamp.

TimeStamp,MachineID,Alarm
2022/08/13 12:32:24,P211,A1
2022/08/14 23:45:00,D566,A2
2022/08/15 05:45:00,P211,A2
2022/08/16 16:00:00,K345,A4
2022/08/17 21:33:00,E435,A2
2022/08/18 12:45:00,E435,A1
2022/08/19 08:34:00,S333,A9
2022/08/20 06:44:00,E435,A4
2022/08/21 04:37:00,N123,A2
2022/08/22 13:00:00,P211,A5
2022/08/23 12:12:00,D566,A2
2022/08/24 17:00:00,E445,A5
2022/08/25 14:00:00,N123,A2
2022/08/26 05:00:00,S333,A1
2022/08/27 05:00:00,K345,A9
2022/08/28 08:00:00,S345,A2
2022/08/29 04:00:00,G545,A4
2022/08/30 07:34:00,N123,A3
2022/08/31 05:00:00,P345,A5
2022/09/01 22:00:00,P211,A2
2022/09/02 13:00:00,D566,A5
2022/09/03 14:00:00,G234,A2
2022/09/04 12:33:00,S345,A4
2022/09/05 07:00:00,N123,A1
2022/09/06 20:00:00,P122,A9
2022/09/07 21:00:00,D566,A8
2022/09/08 13:00:00,G234,A7
2022/09/09 08:00:00,S333,A8
2022/09/10 15:00:00,K245,A7
2022/09/11 19:00:00,D433,A3

Next, a list of machines with additional information for each machines such as it's location and some other category/group information. 

MachineID,Category,Group,Location
N123,NX,SU32,F100
N435,NX,SU32,F200
E435,EX,SU38,F200
E344,EX,SU38,F300
E445,EX,SU38,F200
K245,KX,SU32,F300
K664,KX,SU32,F100
K345,KX,SU32,F100
S345,SX,SU38,F100
S333,SX,SU38,F200
G545,GX,SU38,F300
G234,GX,SU38,F200
P344,PX,SS30,F300
P122,PX,SS30,F100
P211,PX,SS30,F100
P345,PX,SS30,F300
D433,DX,SS32,F200
D434,DX,SS32,F200
D566,DX,SS32,F100
D345,DX,SS32,F300

Once imported into Power BI, I'll link the two tables using the [MachineID] column.

Now, for the list of alarms, I'd like to calculate something called 'Mean time to Interrupt' (MTTI), in hours. This is, essentially, the mean time between two alarms in any given data set. For instance, suppose our complete data set consisted of just 3 alarms, spaced 4 and 6 hours apart. So, my Mean time to Interrupt for the whole data set would be (4 + 6) / 2 = 5 hours.

But, the key is, I would like for this to be a dynamic caluculation, based on user selection on slicers. So, suppose I have four slicers on my report, one each for [ Location ], [ Group ], [ Category ], and [ MachineID ]. Based on these selections, I'd like to display the [ Mean time to Interrupt ] on a data card. For instance, if a user chose a particular MachineID, I'd like the calculated MTTI to consider only the alarms on that particular machine, and calculate the mean value for those only.

For this, first I created an Index column in the Alarms table, then created a calculated column to calculate, in hours, the difference of TimeStamp between any two rows.

Diff = 
    VAR nextIndex = Alarms[Index] - 1
    RETURN
        DATEDIFF( 
            CALCULATE(
                    VALUES(Alarms[Date]),
                    FILTER(ALL(Alarms), Alarms[Index] = nextIndex)
            ),
            Alarms[Date],
            HOUR)

Then, I simply created an average measure as follows:

MTTI = AVERAGE(Alarms[Diff])

This works fine for the entire dataset. I get a Mean Time to Interrupt (MTTI) = 24.24 value considering all records.

However, if I do a slicer selection, say MachineID = D566, I don't get the result I expect.

Looking closely at the data, it seems that the issue is with my calulated column, as it always calculates the difference between any selected row and it's predecessor, even if that particular row is not in the selection user made. So, in the above example, it just calculates the difference between the four selected rows and their predesessors instead of calculating the difference between the selected rows themselves.

Any suggestions on how I can get the result I desire? I'm okay with a different approach to a calulcated column.

[HoangHugo · Accepted solution]

Hi

Your index colum is fix number, so it canot be correct if you use slicer. In this case, create a measure to calculate Diff which can be dynamic apply slicer.

Diff =

var time = SELECTEDVALUE(Timestamp column)

return DATEDIFF(time,MAXX(CALCULATETABLE(Alarm table,REMOVEFILTER(Timestamp column),Timestamp column < Time),Timestamp column)

then we calculate Average

MITI = AVERAGEX(SUMMARIZE(Timestamp Column),Diff)

[v-rzhou-msft]

Hi @Sachintha ,

Please try measure as below.

Dynamic Index = 
CALCULATE(MAX(Alarms[Index]),FILTER(ALLEXCEPT(Alarms,'Table 2'[Category],'Table 2'[Group],'Table 2'[Location],'Table 2'[MachineID]),Alarms[Index]<MAX(Alarms[Index])))

Dynamic_Diff = 
VAR _CURRENT = CALCULATE(MAX(Alarms[TimeStamp]))
VAR _SUMMARIZE = ADDCOLUMNS(ALLSELECTED(Alarms),"DYNAMIC INDEX",[Dynamic Index])
VAR _ADD = ADDCOLUMNS(_SUMMARIZE,"PREVIOUS",MAXX(FILTER(_SUMMARIZE,[Index] = EARLIER([DYNAMIC INDEX])),[TimeStamp]))
VAR _PREVIOUS = MAXX(FILTER(_ADD,[Index] = MAX(Alarms[Index])),  [PREVIOUS])
RETURN
DATEDIFF(_PREVIOUS,_CURRENT,HOUR)

MTTI = AVERAGEX(Alarms,[Dynamic_Diff])

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sachintha]

Thanks for the reply. However this doesn't work for the entire dataset when no filters are applied in any of the slicers. I get a MTTI of 156.75 in that case when it should be 24.24

[HoangHugo · Accepted solution]

Hi

Your index colum is fix number, so it canot be correct if you use slicer. In this case, create a measure to calculate Diff which can be dynamic apply slicer.

Diff =

var time = SELECTEDVALUE(Timestamp column)

return DATEDIFF(time,MAXX(CALCULATETABLE(Alarm table,REMOVEFILTER(Timestamp column),Timestamp column < Time),Timestamp column)

then we calculate Average

MITI = AVERAGEX(SUMMARIZE(Timestamp Column),Diff)

[Sachintha]

@HoangHugo I have a follow up question.

This method works on paper - for a small data set like this - but it fails when the data set is large. My actual data set has thousands of recrods, and will grow weekly. Even with 5 weeks worth of data, it takes a long time and eventually times out.

Is there a bettter, more efficient, way of doing this?

[Sachintha · Accepted solution]

I just rfigured out that this can be achieved with a much simpler method without having to sequentially calculate the time differences using an index.

First, grab the MAX and MIN time stamps of the dataset. This will ensure data adheres to any user selected filters based on slicers.

---

_maxTime = MAX(Alarms[TimeStamp])

_minTime = MIN(Alarms[TimeStamp])

Calculate time differences between the two using DATEDIFF.

---

_timeDiff = DATEDIFF([_minTime], [_maxTime], HOUR)

Then divide the time difference by number of rows minus 1.

MTTI = DIVIDE([_timeDiff], [_countRows] - 1)

This calculates the desired results while adhering to filter selections, and does not take exponentially long time.

[HoangHugo]

Yes, that is a great way. Because I do'nt understand totally your context, so my formula calculate each row to have Diff. And your new way is better.