Join one table if condition is met and another table if another condition is met

[Anonymous]

Let's say I have the following tables:

Table 1:

Order ID	group
1	1
2	2
3	1
4	2
5	2

Table 2:

order ID	name group 1
1	a
3	b

Table 3:

order ID	name group 2
2	c
4	d
5	e

I want to join table 1 to either table 2 or 3 based on condition. If group = 1 then bring name column from table 2, if group = 2, bring name column from table 3. How would I do that?

Thanks

[VahidDM · Accepted solution]

Hi @Anonymous 

Try this code to add a new column :

Name Group = 
Var _A = LOOKUPVALUE('Table 2'[name group 1],'Table 2'[order ID],'Table 1'[Order ID])
Var _B = LOOKUPVALUE('Table 3'[name group 2],'Table 3'[order ID],'Table 1'[Order ID])
return
if('Table 1'[group ]=1,_A,_B)

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[AlexisOlson]

Add a group column to Table 2 and Table 3 and then append them together to get

Then you can merge the appended table with Table 1.

Finally, expand the name column.