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

Reply
yuanye0710
Frequent Visitor

Count distinct items from 1 column that don't exist in another column

Hi, I'd like to write a measure to calculate total distinct items from 1 column that not found in another column. 2 columns are from different tables.

 

I was able to do this my adding a calculated helper column, but just wondering if there is any way to do it without adding an extra column.

 

For example:

Column A from table 1

-----------

AA

AA

AA

BB

CC

CC

DD

DD

EE

 

Column B from table 2

-----------

AA

AA

BB

BB

FF

 

I want to count distinct items from Column A not found in Column B.

 

There are 5 items in Column A not found in Column B:  

-----------

AA

AA

AA

BB

CC

CC

DD

DD

EE

 

And there are 3 distinct counts: CC DD and EE, so the measure should return 3.

 

Thanks!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@yuanye0710 , except can help

 

In A not in B

a new table = except(distinct(TableA[COlumn]), distinct(TableB[COlumn]) )

 

measure

 

a new table = Countrows(except(distinct(TableA[COlumn]), distinct(TableB[COlumn]) ) )

 

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@yuanye0710 , except can help

 

In A not in B

a new table = except(distinct(TableA[COlumn]), distinct(TableB[COlumn]) )

 

measure

 

a new table = Countrows(except(distinct(TableA[COlumn]), distinct(TableB[COlumn]) ) )

 

 

Thanks a lot! it's working for me! 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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