MCode or DAX measure to create filter or new column based on daily total comparison.

[MODUser]

I have a daily data interval table and need assistance with MCode, a measure or custom column to assign "On Site" or "Off Site" based on criteria....where total Work time = total Other time for the entire day, assign "Off Site" else "On Site" and have the assignment for the day assigned to each interval for the individual.

   

[MODUser]

Work, thank you! Had to adjust as the store could be 1 - 3 stores, so I removed "Store" as and where applicable. Thanks Again!

[Anonymous · Accepted solution]

Hello, did it in a lot of steps, hope it helps.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Date", "Full Name", "Store", "Work", "Other"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Date", "Full Name", "Store"}, {{"Sum Work", each List.Sum([Work]), type number}, {"Sum Other", each List.Sum([Other]), type number}}),
#"Added Conditional Column" = Table.AddColumn(#"Grouped Rows", "Location", each if [Sum Work] = [Sum Other] then "Off Site" else "On Site"),
Custom1 = Table.NestedJoin(#"Changed Type",{"Date", "Full Name", "Store"},#"Added Conditional Column",{"Date", "Full Name", "Store"},"Custom"),
#"Expanded Custom" = Table.ExpandTableColumn(Custom1, "Custom", {"Location"}, {"Location"})
in
#"Expanded Custom"