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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
SamOvermars
Helper I
Helper I

How to get a count of customer whose not available in another table

Hello,

I have two list of customer available in two different tables. How can I get the count of customer that are available in Table1 but not available in Table2?

Example below

Table1Table2
CustomerCusomter
Cus1Cus1
Cus2Cus1
Cus3Cus12
Cus4Cus12
Cus5 
Cus6 
1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@SamOvermars 

pls try this

Measure = 
VAR tbl=ADDCOLUMNS('Table 1',"check",LOOKUPVALUE(Table2[Customer],Table2[Customer],'Table 1'[Customer]))
return COUNTROWS(FILTER(tbl,[check]=""))

1.PNG

 





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

Proud to be a Super User!




View solution in original post

3 REPLIES 3
v-cgao-msft
Community Support
Community Support

Hi @SamOvermars ,

 

Please try this measure:

Measure = COUNTROWS(DISTINCT(EXCEPT('Table1','Table2')))

vcgaomsft_0-1657604475978.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Ashish_Mathur
Super User
Super User

Hi,

Write this calculated column formula in Table1

Available in Table2 = LOOKUPVALUE(Table2[Customer],Table2[Customer],Table1[Customer])

Write this measure

Measure = calculate(countrows(Table1),Table1[Available in Table2]=BLANK())

Hope this helps.

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ryan_mayu
Super User
Super User

@SamOvermars 

pls try this

Measure = 
VAR tbl=ADDCOLUMNS('Table 1',"check",LOOKUPVALUE(Table2[Customer],Table2[Customer],'Table 1'[Customer]))
return COUNTROWS(FILTER(tbl,[check]=""))

1.PNG

 





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

Proud to be a Super User!




Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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