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.
Looking to index rows by the distinct value in column #Order. Then Getting the difference of the 2nd value(2:25:48) from column #First time and the 1st value(1:47:31) from column #Last time. That way Im only getting the duration between last time and first time of each unique order.
Would it be easier to transpose all times for each distinct order on a new query? or is it possible using Dax?
Solved! Go to Solution.
Hi @Anonymous ,
Please check if this is what you want:
Create a column like so:
Column =
VAR Index_1 = 'Table'[Index] - 1
VAR CurrentOrder =
CALCULATE (
MAX ( 'Table'[Order] ),
FILTER ( 'Table', 'Table'[Index] = Index_1 + 1 )
)
VAR LastOrder =
CALCULATE (
MAX ( 'Table'[Order] ),
FILTER ( 'Table', 'Table'[Index] = Index_1 )
)
VAR FirstTime = 'Table'[First Time]
VAR LastTime =
IF (
CurrentOrder = LastOrder,
MAXX ( FILTER ( 'Table', 'Table'[Index] = Index_1 ), [Last Time] )
)
VAR DateDiffSeconds =
DATEDIFF ( LastTime, FirstTime, SECOND )
VAR Hours =
TRUNC ( DateDiffSeconds / 3600 )
VAR Minutes =
TRUNC ( ( DateDiffSeconds - Hours * 3600 ) / 60 )
VAR Seconds =
MOD ( DateDiffSeconds - Hours * 3600, 60 )
VAR Result =
IF (
LastTime <> BLANK (),
FORMAT ( Hours, "00" ) & ":"
& FORMAT ( Minutes, "00" ) & ":"
& FORMAT ( Seconds, "00" )
)
RETURN
Result
For more details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Please check if this is what you want:
Create a column like so:
Column =
VAR Index_1 = 'Table'[Index] - 1
VAR CurrentOrder =
CALCULATE (
MAX ( 'Table'[Order] ),
FILTER ( 'Table', 'Table'[Index] = Index_1 + 1 )
)
VAR LastOrder =
CALCULATE (
MAX ( 'Table'[Order] ),
FILTER ( 'Table', 'Table'[Index] = Index_1 )
)
VAR FirstTime = 'Table'[First Time]
VAR LastTime =
IF (
CurrentOrder = LastOrder,
MAXX ( FILTER ( 'Table', 'Table'[Index] = Index_1 ), [Last Time] )
)
VAR DateDiffSeconds =
DATEDIFF ( LastTime, FirstTime, SECOND )
VAR Hours =
TRUNC ( DateDiffSeconds / 3600 )
VAR Minutes =
TRUNC ( ( DateDiffSeconds - Hours * 3600 ) / 60 )
VAR Seconds =
MOD ( DateDiffSeconds - Hours * 3600, 60 )
VAR Result =
IF (
LastTime <> BLANK (),
FORMAT ( Hours, "00" ) & ":"
& FORMAT ( Minutes, "00" ) & ":"
& FORMAT ( Seconds, "00" )
)
RETURN
Result
For more details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @Icey. This is basically what I ended up doing.
I wanted to accomplish this On the query side, but grouping and then cluster indexing and then merging caused relatively long load times.
Thanks again
Hi @Anonymous
if you run into performance problems with the DAX-solution, you could combine these 2 solutions in Power Query:
You'd have to apply this function on Order-level: https://www.youtube.com/watch?v=-3KFZaYImEY
(create Index-column AND apply the function from the blogpost)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
If the columns are already detected as time then try following
datediff(table[first_time],
maxx(filter(table,table[order] =earlier([order]) && table[first_time] <earlier(table[first_time])),table[first_time]),
Second)
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |