Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have a table like so:
TransID | ModAt | ModBy | EventID |
1 | 1:27:20 PM | Bob | Pick |
1 | 1:26:59 PM | Bob | Pick |
2 | 1:26:48 PM | Jill | Pick |
3 | 1:26:24 PM | Harry | Pick |
2 | 1:25:39 PM | Jill | Pick |
3 | 1:24:59 PM | Harry | Pick |
1 | 1:24:09 PM | Bob | Pick |
2 | 1:23:27 PM | Jill | Pick |
3 | 1:20:51 PM | Harry | Pick |
What I want to show is the time differences between each row with that changing dynamically as the table is filtered like so:
TransID | ModAt | ModBy | EventID | TimeDiff(In Seconds) |
1 | 1:27:20 PM | Bob | Pick | 21 |
1 | 1:26:59 PM | Bob | Pick | 11 |
2 | 1:26:48 PM | Jill | Pick | 24 |
3 | 1:26:24 PM | Harry | Pick | 45 |
2 | 1:25:39 PM | Jill | Pick | 40 |
3 | 1:24:59 PM | Harry | Pick | 50 |
1 | 1:24:09 PM | Bob | Pick | 42 |
2 | 1:23:27 PM | Jill | Pick | 156 |
3 | 1:20:51 PM | Harry | Pick | 0 |
Then Filtered:
TransID | ModAt | ModBy | EventID | TimeDiff(In Seconds) |
1 | 1:27:20 PM | Bob | Pick | 21 |
1 | 1:26:59 PM | Bob | Pick | 170 |
1 | 1:24:09 PM | Bob | Pick | 0 |
Thanks!
Solved! Go to Solution.
Hello @STS_Joshua
Give this a try.
TimeDiff(In Seconds) = VAR CurrentRowTime = SELECTEDVALUE ( 'Table'[ModAt] ) VAR TimeSec = DATEDIFF ( CALCULATE ( MAX ( 'Table'[ModAt] ), ALLSELECTED ( 'Table' ), 'Table'[ModAt] < CurrentRowTime ), CurrentRowTime, SECOND ) RETURN IF ( ISBLANK( TimeSec ), 0, TimeSec )
That's odd, the same change works for me:
TimeDiff(In Seconds) TransID = VAR CurrentRowTime = SELECTEDVALUE ( 'Table'[ModAt] ) VAR TransID = SELECTEDVALUE ( 'Table'[TransID] ) VAR TimeSec = DATEDIFF ( CALCULATE ( MAX ( 'Table'[ModAt] ), ALLSELECTED ( 'Table' ), 'Table'[ModAt] < CurrentRowTime, 'Table'[TransID] = TransID ), CurrentRowTime, SECOND ) RETURN IF ( ISBLANK( TimeSec ), 0, TimeSec )
Hello @STS_Joshua
Give this a try.
TimeDiff(In Seconds) = VAR CurrentRowTime = SELECTEDVALUE ( 'Table'[ModAt] ) VAR TimeSec = DATEDIFF ( CALCULATE ( MAX ( 'Table'[ModAt] ), ALLSELECTED ( 'Table' ), 'Table'[ModAt] < CurrentRowTime ), CurrentRowTime, SECOND ) RETURN IF ( ISBLANK( TimeSec ), 0, TimeSec )
Thanks for the answer, I have a followup though. Is there a way to display the DateDiff grouped for each order when unfiltered? Like so:
TransID | ModAt | ModBy | EventID | TimeDiff(In Seconds) |
1 | 1:27:20 PM | Bob | Pick | 21 |
1 | 1:26:59 PM | Bob | Pick | 170 |
2 | 1:26:48 PM | Jill | Pick | 24 |
3 | 1:26:24 PM | Harry | Pick | 45 |
2 | 1:25:39 PM | Jill | Pick | 132 |
3 | 1:24:59 PM | Harry | Pick | 248 |
1 | 1:24:09 PM | Bob | Pick | 0 |
2 | 1:23:27 PM | Jill | Pick | 0 |
3 | 1:20:51 PM | Harry | Pick | 0 |
I tried changing your calculation some but did not get what I wanted:
TimeDiff = VAR CurrentRowTime = SELECTEDVALUE ( 'Table'[ModAt] ) VAR CurrentTransID = SELECTEDVALUE( 'Table'[TransID]) VAR TimeSec = DATEDIFF ( CALCULATE ( MAX ('Table'[ModAt]), 'Table'[TransID]=CurrentTransID, 'Table'[ModAt] < CurrentRowTime ), CurrentRowTime, SECOND ) RETURN IF ( ISBLANK( TimeSec ), 0, TimeSec )
That's odd, the same change works for me:
TimeDiff(In Seconds) TransID = VAR CurrentRowTime = SELECTEDVALUE ( 'Table'[ModAt] ) VAR TransID = SELECTEDVALUE ( 'Table'[TransID] ) VAR TimeSec = DATEDIFF ( CALCULATE ( MAX ( 'Table'[ModAt] ), ALLSELECTED ( 'Table' ), 'Table'[ModAt] < CurrentRowTime, 'Table'[TransID] = TransID ), CurrentRowTime, SECOND ) RETURN IF ( ISBLANK( TimeSec ), 0, TimeSec )
Sorry, but one last question. Is there a way to do that as a calculated column vs. a measurement?
Sure, you can just add a column to your table and make it = the measure.
Column 2 = [TimeDiff(In Seconds) TransID]
My mistake was replacing your ALLSELECTED filter in CALCULATE. With that in there it works. Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |