Forum Discussion
Anonymous
7 years agoNot applicable
Time between two stamps
Dear Data gods, Currently I'm working on a report in which we can monitor our machines active times (might be a bit difficult to explain). And I want to put that in a table, to show the uptime / d...
- Anonymous7 years ago
Fix it with creating a new table and joining it with the current on. All in dax, see formula below :)
Might not be the fastest with creating a fer variables, I still need to learn how to do it, but it gets the job done perfectly!PresenceGrouping = VAR __datetable = GENERATESERIES( MROUND(MIN(PowerBI_Status[Starttime + Date])-1/24, "1:00"), NOW()+1/24, TIME(1,0,0)) VAR __datetable1 = ADDCOLUMNS(__datetable, "_endtime", [Value] + 1/24) VAR __table2 = FILTER( CROSSJOIN(__datetable1, PowerBI_Status), [_endtime] >= PowerBI_Status[Starttime + Date] && [Value] <= PowerBI_Status[Endtime + Date]) VAR __table3 = SELECTCOLUMNS(__table2, [columns here, to much to show]) RETURN ADDCOLUMNS(__table3, "Seconds", SWITCH( TRUE(), [PresenceStart] < [Start] && [PresenceEnd] > [end], 3600, [PresenceStart] < [Start] && [PresenceEnd] <= [end], DATEDIFF([Start], [PresenceEnd], SECOND), [PresenceStart] >= [Start] && [PresenceEnd] > [end], DATEDIFF([PresenceStart], [end], SECOND), DATEDIFF([PresenceStart], [PresenceEnd], SECOND) ) )
Anonymous
7 years agoNot applicable
Fix it with creating a new table and joining it with the current on. All in dax, see formula below :)
Might not be the fastest with creating a fer variables, I still need to learn how to do it, but it gets the job done perfectly!
PresenceGrouping =
VAR __datetable =
GENERATESERIES(
MROUND(MIN(PowerBI_Status[Starttime + Date])-1/24, "1:00"), NOW()+1/24, TIME(1,0,0))
VAR __datetable1 =
ADDCOLUMNS(__datetable, "_endtime", [Value] + 1/24)
VAR __table2 =
FILTER(
CROSSJOIN(__datetable1, PowerBI_Status),
[_endtime] >= PowerBI_Status[Starttime + Date] && [Value] <= PowerBI_Status[Endtime + Date])
VAR __table3 =
SELECTCOLUMNS(__table2, [columns here, to much to show])
RETURN
ADDCOLUMNS(__table3, "Seconds",
SWITCH(
TRUE(),
[PresenceStart] < [Start] && [PresenceEnd] > [end], 3600,
[PresenceStart] < [Start] && [PresenceEnd] <= [end], DATEDIFF([Start], [PresenceEnd], SECOND),
[PresenceStart] >= [Start] && [PresenceEnd] > [end], DATEDIFF([PresenceStart], [end], SECOND),
DATEDIFF([PresenceStart], [PresenceEnd], SECOND)
)
)