New Column Based on Value from Two Existing Columns

[kayjenki]

I am trying to create a new column that takes into consideration values from two existing columns, the first column is a unique ID that is repeated multiple times and the second column is either a Yes or No value associated to the unique ID.

I want the new column to state "Yes" if a yes (column B) exist at any point for the rows associated to the unique ID (column A) and "No" if there are not any rows associated to the unique ID that state yes. Below is an example, I was having a hard time articulating this, hopefully, it makes sense.

Unique ID	Value	New Column
AAAA1234	No	Yes
AAAA1234	No	Yes
AAAA1234	No	Yes
AAAA1234	Yes	Yes
BBBB1234	No	No
BBBB1234	No	No
BBBB1234	No	No
BBBB1234	No	No
CCCC1234	Yes	Yes
CCCC1234	No	Yes
CCCC1234	No	Yes
CCCC1234	No	Yes

[quantumudit · Accepted solution]

Hello @kayjenki 
Thank you for providing sample data.

To achieve the desired result, you can use the following formula to create a calculated column:

New Column - Calculated =
VAR _id = 'Table'[Unique ID]
VAR _values =
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Unique ID] = _id
            ),
            [Value]
        )
    )
VAR _containsYesCount =
    COUNTROWS (
        FILTER (_values, CONTAINSSTRING ([Value], "Yes"))
    )
RETURN
    IF (_containsYesCount = 1, "Yes", "No")

Below is the screenshot of the result, allowing you to effortlessly compare the expected outcomes with the calculated ones:

I am also attachig the Power BI for your reference.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Proud to be a SuperUser

[kayjenki]

Thank you, that worked perfect.

[quantumudit]

Happy to help 😊