March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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 | Out | Type | Start | End |
T_3212 | Out_8 | Waiting | 10.12.2021 16:45:00 | 10.12.2021 19:59:00 |
T_3212 | Out_8 | In | 10.12.2021 8:00:00 | 10.12.2021 15:50:00 |
T_3212 | Out_8 | In | 10.12.2021 6:55:00 | 10.12.2021 7:59:00 |
T_3212 | Out_3 | In | 10.12.2021 4:15:00 | 10.12.2021 7:10:00 |
Solved! Go to 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"
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?
Best Regards,
Community Support Team _ kalyj
@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"
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
128 | |
90 | |
75 | |
56 | |
53 |
User | Count |
---|---|
200 | |
104 | |
101 | |
67 | |
55 |