Forum Discussion
Calculate difference between rows based on slicer selection
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)
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.
Calculate time differences between the two using DATEDIFF._maxTime = MAX(Alarms[TimeStamp])
_minTime = MIN(Alarms[TimeStamp])_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 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?
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.
Calculate time differences between the two using DATEDIFF._maxTime = MAX(Alarms[TimeStamp])
_minTime = MIN(Alarms[TimeStamp])
_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.
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.