cancel
Showing results for
Did you mean:
Regular 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
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]) ) )

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
2 REPLIES 2
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]) ) )

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
Regular Visitor

Thanks a lot! it's working for me!

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors