Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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.
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.
@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()) )
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
85 | |
65 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |