Forum Discussion
Need help to check the missing data in 02 different Columns.
Hello everyone,
I need your help on the following query:
I have table A:
| Number |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
and table B
| 1 |
| 2 |
and C:
| 4 |
| 5 |
I would like to have a new column in Table A like this
| Number | Found on B&C |
| 1 | True |
| 2 | True |
| 3 | False |
| 4 | True |
| 5 | True |
I am trying to use Contain, but it only works with 02 Columns, maybe I should have another approach.
Appreciate your help on this.
Thanks,
Hello! I have entered the following for A:
The following for B:
The following for C:
If you want to show whether a value from A is found in B OR in C, use below:
found_in_b_or_c = IF(COUNTROWS(INTERSECT(VALUES('Table A'[Column1]),UNION(VALUES('Table B'[Column1]), VALUES('Table C'[Column1])))) > 0,TRUE,FALSE)If you want to show if a value from A is found in B AND C, use the below:found_in_b_and_c =IF(COUNTROWS(INTERSECT(VALUES('Table A'[Column1]),INTERSECT(VALUES('Table B'[Column1]), VALUES('Table C'[Column1])))) = COUNTROWS(VALUES('Table A'[Column1])),TRUE,FALSE)Here are the results:
3 Replies
- audreygerred
Super User
Hello! I have entered the following for A:
The following for B:
The following for C:
If you want to show whether a value from A is found in B OR in C, use below:
found_in_b_or_c = IF(COUNTROWS(INTERSECT(VALUES('Table A'[Column1]),UNION(VALUES('Table B'[Column1]), VALUES('Table C'[Column1])))) > 0,TRUE,FALSE)If you want to show if a value from A is found in B AND C, use the below:found_in_b_and_c =IF(COUNTROWS(INTERSECT(VALUES('Table A'[Column1]),INTERSECT(VALUES('Table B'[Column1]), VALUES('Table C'[Column1])))) = COUNTROWS(VALUES('Table A'[Column1])),TRUE,FALSE)Here are the results: Thank you for your suggest solution, but does that also work if Column B doesn't have the same row as Column C?
- audreygerred
You're welcome. Yes, one of the DAX I provided will let you know if the same value is in b or c and the other is only if it's b and c.
