Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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,

1 ACCEPTED SOLUTION
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:

 Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

3 REPLIES 3
Frequent Visitor

Thank you for your suggest solution, but does that also work if Column B doesn't have the same row as Column C?

Super User

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.

 Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

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:

 Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors