cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
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]) ) )

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]) ) )

Frequent Visitor

Thanks a lot! it's working for me!

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 - June 2024

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

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors