Need flag columns for categorical variable

[Anonymous]

Hi,

I have the following dataset

ID	Lane
abc100	Lane 2
abc100	Lane 3
abc200	Lane 2
abc300	Lane 3
abc400	Lane 2
abc400	Lane 3

Based on this dataset I want to create 2 flag columns called Lane 2 flag and Lane 3 flag.

If for a particular ID there exists a Lane 2 then all rows for Lane 2 flag should become Yes else all rows for Lane 2 flag (for that ID) should become No.  The same calculation for Lane 3 flag.

Please find below the expected output. 

*Rows are color-coded based on ID groups for understanding only

ID	Lane	Lane 2 flag	Lane 3 flag
abc100	Lane 2	Yes	Yes
abc100	Lane 3	Yes	Yes
abc200	Lane 2	Yes	No
abc300	Lane 3	No	Yes
abc400	Lane 2	Yes	Yes
abc400	Lane 3	Yes	Yes
abc500	Lane 1	No	No

[KHorseman · Accepted solution]

Using a DAX calculated column, here's what I came up with for Lane 2 Flag:

Lane 2 Flag = IF(
    COUNTROWS(
        FILTER(
            ALL(TableName),
            TableName[ID] = EARLIER(TableName[ID])
            && TableName[Lane] = "Lane 2"
        )
    ) > 0,
    "Yes",
    "No"
)

If that works for you, I'm sure coming up with Lane 3 Flag won't be too difficult from there...

[Anonymous]

Thanks, This works

Could you also explain the logic as I'm not an advanced user.

[KHorseman]

Absolutely. The ALL function returns the entire table, which is then filtered down to only rows that match the filtering criteria in the next two lines. EARLIER means on each row, check that the ID column in the filtered ALL table matches the current row's ID. So in more plain English this formula says:

"On each row in this table, count all rows in the entire table where the ID is the same as this row, and the Lane = 'Lane 2'. If that count is greater than 0, then there is something in Lane 2, so mark this row as 'Yes'. Otherwise, mark it 'No'."

[Anonymous]

Wow, thanks again !