Forum Discussion
Intersection of 3 tables
I have 3 tables with the same columns, including an ID column, but different data. I want the intersection of all these 3 tables and find the list of IDs that are common to all three. How can I do that?
shreyaks1009 , You can update your code as below:-
Table 4 = VAR _tab1 = DISTINCT ( Table1[ID] ) VAR _tab2 = DISTINCT ( Table2[ID] ) VAR _tab3 = DISTINCT ( Table3[ID] ) RETURN DISTINCT ( INTERSECT ( INTERSECT( _tab1, _tab2 ), _tab3 ) )
6 Replies
- Tanushree_Kapse
Impactful Individual
Hi shreyaks1009 ,
You can use RELATED function or LOOKUPVALUE in this case.
https://docs.microsoft.com/en-us/dax/related-function-dax
https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax
I hope this helps.
Mark this as a solution if I aswered your question.
Thanks
Thanks for your help. Unfortunately, these solutions do not help with my problem. Tried to use them. All of the functions are targeted for 2 tables. I am hoping to learn a solution which gives me intersection of 3 similar datasets like in a Venn Diagram.
Samarth_18Community Champion
Hi shreyaks1009 ,
It would be helpful if you could share some sample data with Expected output, it helps to give answer more specifically. In the meantime you can try to create a new table with below code to get your desired output:-
Table 4 = VAR _tab1 = DISTINCT ( Table1[ID] ) VAR _tab2 = DISTINCT ( Table2[ID] ) VAR _tab3 = DISTINCT ( Table3[ID] ) RETURN DISTINCT ( INTERSECT ( UNION ( _tab1, _tab2 ), _tab3 ) )Thanks,
Samarth
Interesting.. I will try this. Thank you!
More info on data -
Table 1 [ID] = {a, b, c, d, e, f, p, q, r, x, y, z}
Table 2 [ID] = {d, e, g, h, k, l, m, r, w, x, y}
Table 3 [ID] = {a, c, d, e, j, k, r, x}
I need the output -
Table 4 [ID] = {d, e, r, x}
For now, I have the following solution -
Table 5 = {Inner Join (Table 1, Table 2)}
Table 6 = {Inner Join (Table 5, Table 3)}
Table 6 is giving me the output I need. Would like to learn a more efficient way.
Apologies if the syntax for describing my problem is confusing. First time posting in the forum. Thank you for your help.
- Samarth_18
shreyaks1009 , You can update your code as below:-
Table 4 = VAR _tab1 = DISTINCT ( Table1[ID] ) VAR _tab2 = DISTINCT ( Table2[ID] ) VAR _tab3 = DISTINCT ( Table3[ID] ) RETURN DISTINCT ( INTERSECT ( INTERSECT( _tab1, _tab2 ), _tab3 ) )
