Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Oleg222
Helper II
Helper II

Unique duration

Hi all. I have a table on which it is necessary to calculate the net duration by "Code" and Type = "In".
For example, for "Code" = T_3212, the measure should show the sum of the durations for rows 2 and 3 in full, and for row 4 the difference between 4:15:00 and 6:55:00 (beginning in row 3) - 11,57 h.
Thank you.

 

link - https://drive.google.com/file/d/1PMThjMu1LEYggGIZdFIXq5Gnjij73H-F/view?usp=sharing

 

 

Code OutTypeStartEnd
T_3212 Out_8 Waiting 10.12.2021 16:45:00 10.12.2021 19:59:00
T_3212 Out_8 In10.12.2021 8:00:00 10.12.2021 15:50:00
T_3212 Out_8 In10.12.2021 6:55:00 10.12.2021 7:59:00
T_3212 Out_3 In10.12.2021 4:15:00 10.12.2021 7:10:00
1 ACCEPTED SOLUTION

Here is how this would look like in Power Query:

 

let
    Джерело = Csv.Document(File.Contents("C:\users\xxx\downloads\data.csv")),
    #"Promoted Headers" = Table.PromoteHeaders(Джерело, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Start", type datetime}, {"End", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Type] = "In")),
    MinutesBetween = (start,end) => List.Generate(()=>start,each _ <= end, each _ + #duration(0,0,1,0)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Minutes", each MinutesBetween([Start],[End])),
    #"Expanded Minutes" = Table.ExpandListColumn(#"Added Custom", "Minutes"),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Minutes", {"Minutes", "Code"}),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Code"}, {{"Total Minutes", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

 

lbendlin_0-1639956917478.png

 

View solution in original post

3 REPLIES 3
v-yanjiang-msft
Community Support
Community Support

Hi @Oleg222 ,

Have your problem been solved? I wonder in your snapshot it's not the duration for row 4 is because its Out is differrent from row 3 or because it's the last time.

For example for code T_1037, the time coincides in Out_8 and Out_6, what's the logic of the calculation, could you please explain more about expected result?

vkalyjmsft_0-1640056806078.png

Best Regards,
Community Support Team _ kalyj

lbendlin
Super User
Super User

@Oleg222 It's a really great question, unfortunately DAX does not yet have the required UNIONX command. I really hope they implement that some day. Microsoft Idea  · UNIONX (powerbi.com)

 

Here is what you would do 

1. for each code  collect all the date ranges that fit your filter ("in")

2. cross join each of these ranges with a "minutes in a day" table

3. UNIONX the resulting lists

4. Get a DISTINCTCOUNT (and subtract 1 as needed)  - that will give you the total minutes you wanted

5. Divide result by 60 to get the hour value

 

So - this is not possible in DAX afaik,  But would it be ok if I do it in Power Query ?

Here is how this would look like in Power Query:

 

let
    Джерело = Csv.Document(File.Contents("C:\users\xxx\downloads\data.csv")),
    #"Promoted Headers" = Table.PromoteHeaders(Джерело, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Start", type datetime}, {"End", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Type] = "In")),
    MinutesBetween = (start,end) => List.Generate(()=>start,each _ <= end, each _ + #duration(0,0,1,0)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Minutes", each MinutesBetween([Start],[End])),
    #"Expanded Minutes" = Table.ExpandListColumn(#"Added Custom", "Minutes"),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Minutes", {"Minutes", "Code"}),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Code"}, {{"Total Minutes", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

 

lbendlin_0-1639956917478.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.