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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.