Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table like an image below:
Because of the logic to implement, I am to convert it to a table using SUMMARIZE Column.
I was able to do this using SQL to implement the logic like so:
CREATE TABLE EventLog
(
EventID INT
, EventName VARCHAR(50) NOT NULL
, EventStartDateTime DATETIME NOT NULL
, EventEndDateTime DATETIME NULL
)
INSERT INTO EventLog(EventID, EventName, EventStartDateTime, EventEndDateTime)
VALUES(100, 'Planting', '20210620 10:34:09 AM','20211018 10:54:49 PM')
,(200, 'Foundation', '20200420 10:34:09 AM','20211018 10:54:49 PM')
,(300, 'Seeding', '20210410 10:27:19 AM','')
,(400, 'Spreading', '20220310 10:24:09 PM','');
And I have a query like the below against the above table that I want to convert to a table in Power BI using SUMMARIZE Column
;WITH CTE AS (
SELECT EventID,EventName,EventStartDateTime,IIF(EventEndDateTime = '',GETUTCDATE(),EventEndDateTime) EventEndDateTime
FROM EventLog
UNION ALL
SELECT EventID,EventName, DATEADD(month, DATEDIFF(month, 0, DATEADD(month , 1 , EventStartDateTime)), 0) , EventEndDateTime
FROM CTE
WHERE DATEADD(month, DATEDIFF(month, 0, DATEADD(month , 1 , EventStartDateTime)), 0) <= EventEndDateTime
)
SELECT EventID,EventName,YEAR(EventStartDateTime),DATENAME(MONTH,EventStartDateTime),DATEDIFF(second, EventStartDateTime, n_EventStartDateTime) / 3600.0
FROM (
SELECT EventID,EventName,EventStartDateTime,LEAD(EventStartDateTime,1,EventEndDateTime) OVER(PARTITION BY EventID,EventName ORDER BY EventStartDateTime) n_EventStartDateTime
FROM CTE
) t1
This gives me result like:
My requirement is to convert the solution into a table in Power BI. I attempted to use SUMMARIZE Column to get it done but I am currently not getting it to reproduce the same result yet. I will appreciate any help on this.
Not surprising that you failed in achieving that goal with simply SUMMARIZE() in DAX; it's purely a data shaping task where PQ is supposed to come into play! Of coz, complex enough even for PQ.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY5LCgIxEESvMmQ9LdWd9pcDuBZchiwCE2RAMiLj/c0HcbbuGurVq/beMGBGc33EvM75Xk6BMOFAgoHhrDqcd2hQSxjEp0HE7dVpT8LoS1aJy/LOU1znJXccBP1TZBtxS2navqMFrxY5Ov5aGq4df75S/BWEYGuhyGUza0L4AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EventID = _t, EventName = _t, EventStartDateTime = _t, EventEndDateTime = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"EventID", Int64.Type}, {"EventName", type text}, {"EventStartDateTime", type datetime}, {"EventEndDateTime", type datetime}}),
#"Replaced null" = Table.ReplaceValue(#"Changed Type", null, DateTime.LocalNow(), Replacer.ReplaceValue, {"EventEndDateTime"}),
// essential step to replicate the logic of the recursive cte
n_Interval = Table.AddColumn(
#"Replaced null",
"n_Month",
each let
diff_yr = Date.Year([EventEndDateTime])-Date.Year([EventStartDateTime]),
diff_m = Date.Month([EventEndDateTime])-Date.Month([EventStartDateTime]),
n = diff_yr*12+diff_m, l = List.Accumulate({1..n},{[EventStartDateTime]},
(s,c) => s & {Date.StartOfMonth(Date.AddMonths([EventStartDateTime],c))}),
cols = {l, List.Skip(l)&{[EventEndDateTime]}}
in Table.FromColumns(cols, {"Year","Month"})
),
#"Expanded n_Month" = Table.ExpandTableColumn(n_Interval, "n_Month", {"Year","Month"}),
TotalHours = Table.AddColumn(#"Expanded n_Month", "Hours", each Number.Round(Duration.TotalHours([Month]-[Year]),2)),
#"Transformed Cols" = Table.TransformColumns(TotalHours, {{"Year", each DateTime.ToText(_, "yyyy")}, {"Month", each DateTime.ToText(_, "MMMM")}}),
#"Removed Columns" = Table.RemoveColumns(#"Transformed Cols",{"EventStartDateTime", "EventEndDateTime"})
in
#"Removed Columns"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hello @CNENFRNL
Thank you for the post. I must say that I really love this. The result is not 100 per cent perfect though. For example, hours in March and July are 744 respectively as their end dates were still open throughout that month.
Please can you provide an example table of input data
and an example table of desired output with a brief description.
Rather than copy and paste SQL code. Thanks. 😀
User | Count |
---|---|
96 | |
85 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |