Complex count first occurrence per date.

[UserBI_PA]

Hi magicians.
I need your wisdom to create a calculated column called 'CPU' based on the following conditions:

- Per each day ('Date' column)
- The first occurrence of Placa column
- That fulfill tow conditions:  Type colmun = 'x' and Status column = 'close' 

If everything above is true then mark '1' on 'CPU' column.

Example of the desired result:

Date	Type	Status	Placa	CPU
1/01/2022	x	open	aaa	0
3/01/2022	y	close	bbb	0
4/01/2022	y	close	aaa	0
1/01/2022	y	open	aaa	0
2/01/2022	y	close	ccc	0
2/01/2022	y	open	aaa	0
2/01/2022	x	close	aaa	1
2/01/2022	x	open	bbb	0
3/01/2022	y	open	ccc	0
3/01/2022	x	close	aaa	1
3/01/2022	x	close	aaa	0
3/01/2022	x	close	aaa	0
3/01/2022	x	close	ccc	1
4/01/2022	x	open	aaa	0
1/01/2022	y	close	bbb	0
2/01/2022	x	close	ccc	1
4/01/2022	y	open	bbb	0
4/01/2022	x	close	ccc	1
1/01/2022	x	close	bbb	1
1/01/2022	x	close	bbb	0

I manage to do this wrapping my dataset with python script however when working with bigger data the script's process never ends.
Finally, I partially solved this problem with DAX  but only works when my visual is in days, when the context change to months everything breaks. This is my spell:

CPU = CALCULATE(
DISTINCTCOUNT(Table1[Placa]),
FILTER(Table1,Table1[Status]="close"),
FILTER(Table1,Table1[Type]=X||Table1[Type]=x)
)

Thank you in advance!

[AlexisOlson · Accepted solution]

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 )