Forum Discussion
Solved
Search for text value in column based on another Column
Hi all. Got a case of the Mondays it seems.
I'm trying to get a calculated column in that returns if any row within an ID group has a specific value, to return an answer for the entire ID Group.
For example, source data
| ID | Value |
| 1 | A |
| 1 | B |
| 1 | A |
| 2 | A |
| 2 | A |
| 3 | B |
| 3 | B |
| 3 | B |
Expected outcome:
| ID | Value | Expected Outcome |
| 1 | A | Yes |
| 1 | B | Yes |
| 1 | A | Yes |
| 2 | A | No |
| 2 | A | No |
| 3 | B | Yes |
| 3 | B | Yes |
| 3 | B | Yes |
So if any row within ID group 1 has a B value at any point, then the new column will show Yes for all ID 1 rows. Otherwise return a No value for all.
Expecting something simple but the brain hasn't woken up fully it seems!
Please try the below.
Status = var _count = CALCULATE(COUNTROWS('Table'),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Value] = "B")) return IF(_count>0,"Yes","No")
2 Replies
Please try the below.
Status = var _count = CALCULATE(COUNTROWS('Table'),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Value] = "B")) return IF(_count>0,"Yes","No")Even the most fundamental Excel worksheet formula is enough to solve it,
