Forum Discussion
Making new column based on whether value from another column in same table is in a different table
Hi,
I have two tables, A and B.
I want to make a new column in table A, such that if a value in another column in table A is in table B, then we set the value to 1, else we set it to 0.
Example:
TableA
| Another column |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
TableB
| B column |
| 1 |
| 3 |
Resulting table
| Another column | New column |
| 1 | 1 |
| 2 | 0 |
| 3 | 1 |
| 4 | 0 |
| 5 | 0 |
I have googled high and low and tried this:
New Column = IF(
CALCULATE(COUNTROWS(Table2), FILTER(Table2, Table2[Value]=Table1[Value])) > 0,
1,0)
But this sets everything to 1 in the new column.
Any help would be really appreciated.
- Anonymous6 years ago
Anonymous Please try below calculated column in tableA
Column = IF(TableA[Another column] IN VALUES(TableB[Column]),1,0)If it helps accept solution
2 Replies
- AnonymousNot applicable
Anonymous Please try below calculated column in tableA
Column = IF(TableA[Another column] IN VALUES(TableB[Column]),1,0)If it helps accept solution
- amitchandak
Super User
You can get based on lookup values or By filter function
New Column in A = maxx(filter(TableB,Table[Another Column]= TableB[B column]),[B column]) New Column = If(isblank([New Column in A]),0,1)Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601