Forum Discussion
New Column Based on Value from Two Existing Columns
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 |
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
3 Replies
- quantumudit
Super User
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 SuperUserThank you, that worked perfect.
- quantumudit
Happy to help 😊
