Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a dataset as shown below that I would like to calculate the average duration of each type based on the difference between the start and end times of each group.
Group | Type | StartTime | EndTime |
10001 | A | 2/11/21 8:12:03 | 2/11/21 8:12:23 |
10001 | A | 2/11/21 8:12:45 | 2/11/21 8:14:59 |
10002 | B | 2/11/21 9:15:05 | 2/11/21 9:16:20 |
10002 | B | 2/11/21 9:16:25 | 2/11/21 9:16:50 |
10002 | B | 2/11/21 9:16:55 | 2/11/21 9:17:10 |
10002 | B | 2/11/21 9:17:17 | 2/11/21 9:17:46 |
10003 | C | 2/11/21 9:18:03 | 2/11/21 9:18:30 |
10003 | C | 2/11/21 9:18:34 | 2/11/21 9:18:58 |
10003 | C | 2/11/21 9:19:02 | 2/11/21 9:19:58 |
10003 | C | 2/11/21 9:20:06 | 2/11/21 9:20:47 |
10004 | A | 2/11/21 9:21:06 | 2/11/21 9:21:48 |
10004 | A | 2/11/21 9:21:56 | 2/11/21 9:22:10 |
10004 | A | 2/11/21 9:22:13 | 2/11/21 9:22:37 |
The desired output matrix would look something like this:
Type | Average Duration (Seconds) |
A | 133.5 |
B | 161 |
C | 164 |
I have been able to make a DAX measure that calculates the difference between the first occurance of A and the last occurance of A, etc., but I have been unable to group the measure. The measure:
PartFabTime = CALCULATE ( DATEDIFF ( MIN ( Log[StartTime] ), MAX ( Log[EndTime]) , HOUR ) )
Anyone have any ideas?
Solved! Go to Solution.
Create column as:
Duration =
CALCULATE(DATEDIFF(MAX('Log'[StartTime]),MAX('Log'[EndTime]),SECOND))
Create a measure as:
PartFabTime =
AVERAGEX(
FILTER(
ALL('Log'),
'Log'[Type]=MAX('Log'[Type])
),
'Log'[Duration]
)
Here is the output:
If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
Create column as:
Duration =
CALCULATE(DATEDIFF(MAX('Log'[StartTime]),MAX('Log'[EndTime]),SECOND))
Create a measure as:
PartFabTime =
AVERAGEX(
FILTER(
ALL('Log'),
'Log'[Type]=MAX('Log'[Type])
),
'Log'[Duration]
)
Here is the output:
If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
This is easier if you add a calculated column on the Log table
Seconds = DATEDIFF ( Log[StartTime], Log[EndTime] , SECOND )
Then you can write a measure that references that column:
CALCULATE ( AVERAGE ( Log[Seconds] ), ALLEXCEPT ( Log, Log[Type] ) )
Otherwise, you need to do that calculation inside an iterator, which isn't particularly efficient computationally.
@AlexisOlson I have tried something similar, however, the calculated column will only calculate the average for each row, and will not account for the gap in time between the rows.
Ah, OK. That does change the requirement since it isn't really an "average" anymore.
How about this?
SecondsPerType =
VAR CurrType = VALUES ( Log[Type] )
VAR StartTime = CALCULATE ( MAX ( Log[StartTime] ), ALLSELCTED ( Log ), Log[Type] IN CurrType )
VAR EndTime = CALCULATE ( MAX ( Log[EndTime] ), ALLSELCTED ( Log ), Log[Type] IN CurrType )
RETURN
DATEDIFF ( StartTime, EndTime, SECOND )
So I took your example, which didn't immediately work, and tried modifying it a bit to see if I could get it to work, but I keep getting this output:
Input:
SecondsPerType =
VAR CurrType = SELECTEDVALUE ( Log[Type] )
VAR StartTime = CALCULATE ( MAX( Log[StartTime] ), ALLSELECTED( Log[Type] )) IN CurrType
VAR EndTime = CALCULATE ( MAX ( Log[EndTime] ), ALLSELECTED( FabLog[Type] )) IN CurrType
RETURN
DATEDIFF ( StartTime, EndTime, SECOND )
Ouput:
Multiple arguements aren't allowed in the ALLSELECTED function when the first argument is a table reference.
Any ideas? For some reason I think it is expecting the CurrType to be a table?
Yes, I wrote it so that CurrType could be multiple values (so that totals would be more likely to work as expected). If you don't care about totals and use SELECTEDVALUE, which gives a single value instead of a list of value(s), then you'd have something like this:
SecondsPerType =
VAR CurrType = SELECTEDVALUE ( Log[Type] )
VAR StartTime = CALCULATE ( MAX ( Log[StartTime] ), ALLSELECTED ( Log ), Log[Type] = CurrType )
VAR EndTime = CALCULATE ( MAX ( Log[EndTime] ), ALLSELECTED ( Log ), Log[Type] = CurrType )
RETURN
DATEDIFF ( StartTime, EndTime, SECOND )
Ah I was just trying different functions to see if I could get it to work. A list of values is what I would be expecting as a result, so I changed it back to VALUES. It is now returning a value of 0 for every row, and there is no error. Any thoughts?
EDIT:
After some experimenting, it is calculating the difference between the most recent start time and most recent end time, instead of doing it by group.
PBIX file:
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
60 | |
55 |
User | Count |
---|---|
179 | |
108 | |
105 | |
71 | |
70 |