Count unique values that appear in several tables

[Anonymous]

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!

[v-chenwuz-msft · Accepted solution]

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]

@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()) )