Forum Discussion
carlbh
4 years agoHelper I
Combining data from two tables.
I have a challenge I am trying to overcome and I am hoping someone here has had a similar issue and can assist. I have a table containing all the cases managed by a helpdesk, date logged, duration,...
- 4 years ago
Here is one way of achieving what you are after.
First of all, you need to split the Logged Date Time into date and time columns. In Power Query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBBDoAgDAS/Yjib0KWgwFcI//+GKCWt0cteJjtbaM0RPBUfKGCjUonc7iDp+v7iQEV6CEaGL+fKsxlGsnBWf65x9tn42e5LP96ZJo/qp7WfzP7x0z9Gpg8f98vLzptPnO35os9Gb3hc9aIcpPwUjvV9/QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Logged Date Time" = _t, #"Case ID" = _t, #"Duration (Mins)" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Logged Date Time", type datetime}, {"Case ID", Int64.Type}, {"Duration (Mins)", Int64.Type}}), #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Logged Date Time", type text}}, "es-ES"), "Logged Date Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Logged Date Time.1", "Logged Date Time.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Logged Date Time.1", type date}, {"Logged Date Time.2", type time}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Logged Date Time.1", "Logged Date"}, {"Logged Date Time.2", "Logged Time"}}) in #"Renamed Columns"Which gets you this:
Nest create a Date Table using:
Date Table = VAR MinD = MIN('Table'[Logged Date]) VAR MaxD = MAX('Table'[Logged Date]) RETURN CALENDAR(MinD, MaxD)Create a relationship between the Date Table and the Logged Date field. The model looks like this:
Create a Measure for the duration:
Sum Duration = SUM('Table'[Duration (Mins)]) + 0And finally create the visual using the date field from the Date Table and the [Sum Duration] measure to get:
I've attached the sample PBIX file
Anonymous
4 years agoNot applicable
Create calculated table of dates using CALENDAR or CALENDARAUTO, then add calculated column with sum of durations, something like this total_duration=CALCULATE(SUMX('Case Data','Case Data'[Duration (Mins)])).