Intersection of 3 tables

[shreyaks1009]

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?

[Samarth_18]

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

[shreyaks1009]

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 · Accepted solution]

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

[shreyaks1009]

This helps, thanks!

[Tanushree_Kapse]

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

[shreyaks1009]

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.