Forum Discussion

Sachintha's avatar
Sachintha
Helper III
3 years ago
Solved

Calculate difference between rows based on slicer selection

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,A...
Need Help
Show and Tell
Tips and Tricks
  • HoangHugo's avatar
    HoangHugo
    3 years ago

    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's avatar
    Sachintha
    3 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
Sachintha
Helper III
3 years ago

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