Checking if value exists in mutliple tables with Switch

[JBusque]

Hello. I have a Master Table which is a Distinctunion of 3 different Tables. 

My intention is to have it check based on the Distinct Union which table the Identifier exists in based on a Priority.

So for example it checks for Identifier 6465865. Finds it in Table 1 and Table 2. Based on Priority chooses the first option of Table 1 by returning a text value indicating this.

I believe this would work with a Switch but am having trouble making it work.

[TomMartens · Accepted solution]

Hey @JBusque ,

assuming there is a relationship between the master table and the three "detail" tables, with the master table on the one-side and the detail tables on the many-side of the relationship you can use the below DAX to create a calculated column in the master table:

checking the existence = 
var relatedT1 = countrows( relatedtable( 'T1' ) )
var relatedT2 = countrows( relatedtable( 'T2' ) )
var relatedT3 = countrows( relatedtable( 'T3' ) )
return
switch(
true()
, not( isblank( relatedT1 ) ), "exists in T1"
, not( isblank( relatedT2 ) ), "exists in T2"
, not( isblank( relatedT3 ) ), "exists in T3"
"does not exist in any table"
) 

Hopefully, this provides what you are looking for.

Regards,

Tom

[JBusque]

Perfect, Thank you!