DAX 'Contain' for columns in different tables with conditional switch

[shakir99]

Hi all, 
(A screenshot of the table structure has been included below)

I'm trying to essentially show unique ID values in table 3 (that are retrieved from table 1 and 2) and show the 'latest' phase based on a condition along the lines of 'if table3[id] exists in table1[id], show table1[phase], else if table3[id] exists in table2[id], show table2[phase], else blank()'.

So far, I've done Distinct Union of table1[id] and table2[id] to create table3[id]. Then was planning to do Switch with Contains DAX function to return true or false on whether the table3[id] exists in table1 or table 2 and then show their corresponding phase value  however I saw that contains function can only work within a specified table and not accross tables. 

Any assistance here would be greatly appreciated! (Target state is table 3).

[v-yalanwu-msft · Accepted solution]

Hi,  @shakir99 ，

You could try it.

New = 
var _2= EXCEPT(VALUES('Table2'[ID]),VALUES(Table3[ID]))
var _1=EXCEPT(VALUES(Table1[ID]),UNION(VALUES(Table2[ID]),VALUES(Table3[ID])))
 return UNION(SUMMARIZE(_2,[ID],"Phase","Middle"),SUMMARIZE(_1,[ID],"Phase","Starting"),SUMMARIZE('Table3',[ID],[Phase]))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @shakir99 ,

Try to create two measure.

Measure = MAX('Table1'[ID]) in SUMMARIZE( INTERSECT('Table1','Table3'),[ID])

Measure 2 = MAX('Table2'[ID]) in SUMMARIZE( INTERSECT('Table2','Table3'),[ID])

the final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shakir99]

Hi @v-yalanwu-msft , 
Thanks for your answer however instead of two output measures - is there anyway to just output one table as shown in table 3? That is the desired output. 

[amitchandak]

@shakir99 , Try a new table

Union(Table2, calculatetable(Table1, filter(Table1, not Table[ID] in except(Table2[ID] , Table1[ID]) )))

[shakir99]

and when you say "not Table[ID]" in the suggested DAX formula, which table should that be? 1 or 2?

[shakir99]

Thanks @amitchandak for the answer however, is there a way I could do this with three tables instead of two? And specify the columns I'd like to be included?

I've posted another question  here finer detailing my query:
https://community.powerbi.com/t5/Desktop/DAX-Contain-for-columns-in-different-tables-with-conditiona...