Forum Discussion
Solved
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...
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"
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"