cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
leharkapil Member
Member

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
1 ACCEPTED SOLUTION

Accepted Solutions
KHorseman Super Contributor
Super Contributor

Re: Need flag columns for categorical variable

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

 


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
4 REPLIES 4
KHorseman Super Contributor
Super Contributor

Re: Need flag columns for categorical variable

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

 


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
leharkapil Member
Member

Re: Need flag columns for categorical variable

Thanks, This works

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

KHorseman Super Contributor
Super Contributor

Re: Need flag columns for categorical variable

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


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
leharkapil Member
Member

Re: Need flag columns for categorical variable

Wow, thanks again !

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 306 members 3,296 guests
Please welcome our newest community members: