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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Count unique values that appear in several tables

Hello everyone,

 

I hope someone can help.

 

I have 5 tables that all contain the column "material no.".

Now I want to count how many of these material no. appear in all 5 tables.

 

Also it would be great to see how many only appeared in 3 tables, 2 tables etc.

 

Thank you very much!

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

Hi @Starter,

these tables are the same field

First create a new table with this formula

All_Table =

var table_ = UNION('Table1','Table2','Table3','Table4','Table5')

return

DISTINCT(table_)

 

After that, create a column

how many times appear=

var table_1 =  IF(COUNTROWS(FILTER('Table1',[material no.]=EARLIER(All_Table[material no.])))>=1,1,0)

var table_2 =  IF(COUNTROWS(FILTER('Table2',[material no.]=EARLIER(All_Table[material no.])))>=1,1,0)

var table_3 =  IF(COUNTROWS(FILTER('Table3',[material no.]=EARLIER(All_Table[material no.])))>=1,1,0)

var table_4 =  IF(COUNTROWS(FILTER('Table4',[material no.]=EARLIER(All_Table[material no.])))>=1,1,0)

var table_5 =  IF(COUNTROWS(FILTER('Table5',[material no.]=EARLIER(All_Table[material no.])))>=1,1,0)

return

table_1+table_2+table_3+table_4+table_5

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-chenwuz-msft
Community Support
Community Support

Hi @Starter,

these tables are the same field

First create a new table with this formula

All_Table =

var table_ = UNION('Table1','Table2','Table3','Table4','Table5')

return

DISTINCT(table_)

 

After that, create a column

how many times appear=

var table_1 =  IF(COUNTROWS(FILTER('Table1',[material no.]=EARLIER(All_Table[material no.])))>=1,1,0)

var table_2 =  IF(COUNTROWS(FILTER('Table2',[material no.]=EARLIER(All_Table[material no.])))>=1,1,0)

var table_3 =  IF(COUNTROWS(FILTER('Table3',[material no.]=EARLIER(All_Table[material no.])))>=1,1,0)

var table_4 =  IF(COUNTROWS(FILTER('Table4',[material no.]=EARLIER(All_Table[material no.])))>=1,1,0)

var table_5 =  IF(COUNTROWS(FILTER('Table5',[material no.]=EARLIER(All_Table[material no.])))>=1,1,0)

return

table_1+table_2+table_3+table_4+table_5

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , You need to have a common material dimension and create a new measure like

 


sumx( values(material[material]), if(countrows(Table1)>0,1,0) + if(countrows(Table2)>0,1,0) + if(countrows(Table3)>0,1,0) + if(countrows(Table4)>0,1,0) + if(countrows(Table5)>0,1,0))

 

you can filter for =5 for having all material , visual level filter

 

or

 

sumx( values(material[material]) , if( if(countrows(Table1)>0,1,0) + if(countrows(Table2)>0,1,0) + if(countrows(Table3)>0,1,0) + if(countrows(Table4)>0,1,0) + if(countrows(Table5)>0,1,0) = 5,1, blank()) )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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