Forum Discussion
Complex count first occurrence per date.
- 4 years ago
DAX cannot distinguish between identical rows, so you need some sort of ordering index to know which is "first". As a result, this is probably better done in the query editor (which can easily generate an index column).
Here's an example of what the query might look like if you did this entirely in the query editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3MNQ3MjAyUtJRqgDi/ILUPCCVmJioFKsTrWSMJF0JxMk5+cWpQDopKQksb4JDHqbfEE0ezXgjHNqTk5OxyuPRXoHFdnR5qHaY49E9B5WGWW5MwHRaycPsN8EfNehBix41uAIHm/GVmIGDbju6dvSEg249XvlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Type = _t, Status = _t, Placa = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Type", type text}, {"Status", type text}, {"Placa", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Type] = "x") and ([Status] = "close")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Date", "Placa"}, {{"Index", each List.Min([Index]), type number}}), #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Grouped Rows", {"Index"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Index"}, {"CPU"}), #"Transformed Column" = Table.TransformColumns(#"Expanded Grouped Rows", {{"CPU", each if _ = null then 0 else 1, Int64.Type}}) in #"Transformed Column"(Try pasting this into the Advanced Editor in a new blank query.)
Alternatively, you can just add the index column in the query editor and do the rest in DAX.
CPU = VAR _MinIndex = CALCULATE ( MIN ( Table1[Index] ), ALLEXCEPT ( Table1, Table1[Date], Table1[Placa] ), Table1[Status] = "close", Table1[Type] = "x" ) RETURN IF ( Table1[Index] = _MinIndex, 1, 0 )
DAX cannot distinguish between identical rows, so you need some sort of ordering index to know which is "first". As a result, this is probably better done in the query editor (which can easily generate an index column).
Here's an example of what the query might look like if you did this entirely in the query editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3MNQ3MjAyUtJRqgDi/ILUPCCVmJioFKsTrWSMJF0JxMk5+cWpQDopKQksb4JDHqbfEE0ezXgjHNqTk5OxyuPRXoHFdnR5qHaY49E9B5WGWW5MwHRaycPsN8EfNehBix41uAIHm/GVmIGDbju6dvSEg249XvlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Type = _t, Status = _t, Placa = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Type", type text}, {"Status", type text}, {"Placa", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Type] = "x") and ([Status] = "close")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Date", "Placa"}, {{"Index", each List.Min([Index]), type number}}),
#"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Grouped Rows", {"Index"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Index"}, {"CPU"}),
#"Transformed Column" = Table.TransformColumns(#"Expanded Grouped Rows", {{"CPU", each if _ = null then 0 else 1, Int64.Type}})
in
#"Transformed Column"
(Try pasting this into the Advanced Editor in a new blank query.)
Alternatively, you can just add the index column in the query editor and do the rest in DAX.
CPU =
VAR _MinIndex =
CALCULATE (
MIN ( Table1[Index] ),
ALLEXCEPT ( Table1, Table1[Date], Table1[Placa] ),
Table1[Status] = "close",
Table1[Type] = "x"
)
RETURN
IF ( Table1[Index] = _MinIndex, 1, 0 )