Conversing DAX to custom column Query Setting

[annie_liu]

Hello - calculate how many holidays employee should taken since offboarding date 

I twas trying figure out how to do this in the power query.

Holiday_days = var _start_date = IF( [OnBoardText] > MIN('Calendar'[Date]) ,[OnBoardText], MIN('Calendar'[Date]))

var _t= FILTER('Calendar','Calendar'[Date] >= _start_date && 'Calendar'[Date] <= [OffBoardText])

return

SUMX(_t,[is_holiday])

Calendar [Date] is another table.. while the onboard is within the same table

[v-stephen-msft · Accepted solution]

Hi @annie_liu ,

My approach was a bit complicated, but I made it. You can refer to the following M code. You can also download my attachment to see details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLUtdA1MoawjXQNdY0MlWJ1opWMkERgTCDHDCwJUg4TNAXriAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, OnBoardText = _t, OffBoardText = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"OnBoardText", type date}, {"OffBoardText", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Min(#"Table (2)","Date")),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Date"}, {"Custom.Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom.Date", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "MinDate", each if [OnBoardText] = null then [Custom.Date] else if [Custom.Date] < [OnBoardText] then [OnBoardText] else [Custom.Date]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom.Date"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Custom", each #"Table"),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"Date", "Holiday Count"}, {"Date", "Holiday Count"}),
    #"Added Custom3" = Table.AddColumn(#"Expanded Custom1", "Custom", each if [MinDate]<=[Date] and [OffBoardText]>=[Date] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom3", each ([Custom] = 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns1", each ([Holiday Count] = 1)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows1", {"Id"}, {{"All Rows", each _, type table [Id=nullable number, OnBoardText=nullable date, OffBoardText=nullable date, MinDate=date, Date=date, Holiday Count=number]}, {"Holiday Count", each Table.RowCount(_), Int64.Type}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Id", "OnBoardText", "OffBoardText"}, {"Id.1", "OnBoardText", "OffBoardText"})
in
    #"Expanded All Rows"

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.