Forum Discussion
Anonymous
4 years agoNot applicable
Calculating Intervals Between Separate Rows of Data
Hi everyone! Really new to PowerBI (when I say new, I just started fiddling with it a week ago) and I'm having a problem working on this with DAX. I found a similar thread but it doesn't deal with t...
- 4 years ago
Does it have to be DAX? This can be solved quicker in Power Query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdAxCsMwDAXQqxjPgVhfVtr6FNlNxq4ttPeHOKoFcYjTzV/o8YVz9uT84Ofn5/t+OX07GWkEHEkK4TDgMliG7NFDCInCYWCIL1BUZJlKs5K4I/hDoiiRbgvpJQ35tUzdFkrSENhhtx3hhqD+mmU2cj8h2FZ0YwOaxPYfvX1JqA01cgHLCg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Person = _t, START = _t, END = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"START", type datetime}, {"END", type datetime}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Person"}, {{"Rows", each _, type table [Index=nullable number, Person=nullable text, START=nullable datetime, END=nullable datetime]}}), idx = (tbl)=>Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type), #"Added Index" = Table.AddColumn(#"Grouped Rows","Rows2",each idx([Rows])), diff = (tbl)=> Table.AddColumn(tbl, "Difference", each if [Index]=0 then null else if [START]- tbl{[Index]-1}[END] <= #duration(0,4,0,0) then [START]- tbl{[Index]-1}[END] else null), #"Added Custom" = Table.AddColumn(#"Added Index","Rows3",each diff([Rows2])), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Rows3"}), #"Expanded Rows3" = Table.ExpandTableColumn(#"Removed Other Columns", "Rows3", {"ID", "Person", "START", "END","Difference"}, {"ID", "Person", "START", "END", "Difference"}) in #"Expanded Rows3"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
lbendlin
4 years agoSuper User
Does it have to be DAX? This can be solved quicker in Power Query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdAxCsMwDAXQqxjPgVhfVtr6FNlNxq4ttPeHOKoFcYjTzV/o8YVz9uT84Ofn5/t+OX07GWkEHEkK4TDgMliG7NFDCInCYWCIL1BUZJlKs5K4I/hDoiiRbgvpJQ35tUzdFkrSENhhtx3hhqD+mmU2cj8h2FZ0YwOaxPYfvX1JqA01cgHLCg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Person = _t, START = _t, END = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"START", type datetime}, {"END", type datetime}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Person"}, {{"Rows", each _, type table [Index=nullable number, Person=nullable text, START=nullable datetime, END=nullable datetime]}}),
idx = (tbl)=>Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type),
#"Added Index" = Table.AddColumn(#"Grouped Rows","Rows2",each idx([Rows])),
diff = (tbl)=> Table.AddColumn(tbl, "Difference", each if [Index]=0 then null else if [START]- tbl{[Index]-1}[END] <= #duration(0,4,0,0) then [START]- tbl{[Index]-1}[END] else null),
#"Added Custom" = Table.AddColumn(#"Added Index","Rows3",each diff([Rows2])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Rows3"}),
#"Expanded Rows3" = Table.ExpandTableColumn(#"Removed Other Columns", "Rows3", {"ID", "Person", "START", "END","Difference"}, {"ID", "Person", "START", "END", "Difference"})
in
#"Expanded Rows3"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".