topic Re: Intersection of 3 tables in DAX Commands and Tips

Intersection of 3 tables

shreyaks1009 — Thu, 19 Aug 2021 07:16:26 GMT

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?

Re: Intersection of 3 tables

Tanushree_Kapse — Thu, 19 Aug 2021 08:13:38 GMT

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

Re: Intersection of 3 tables

Samarth_18 — Thu, 19 Aug 2021 08:21:36 GMT

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

Re: Intersection of 3 tables

shreyaks1009 — Thu, 19 Aug 2021 08:37:19 GMT

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.

Re: Intersection of 3 tables

shreyaks1009 — Thu, 19 Aug 2021 08:43:27 GMT

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.

Re: Intersection of 3 tables

Samarth_18 — Thu, 19 Aug 2021 08:54:59 GMT

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

Re: Intersection of 3 tables

shreyaks1009 — Thu, 19 Aug 2021 16:09:33 GMT

This helps, thanks!