Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Need flag columns for categorical variable

Hi,

 

I have the following dataset

 

IDLane
abc100Lane 2
abc100Lane 3
abc200Lane 2
abc300Lane 3
abc400Lane 2
abc400Lane 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

IDLaneLane 2 flagLane 3 flag
abc100Lane 2YesYes
abc100Lane 3YesYes
abc200Lane 2YesNo
abc300Lane 3NoYes
abc400Lane 2YesYes
abc400Lane 3YesYes
abc500Lane 1NoNo
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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...

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks, This works

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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'."