creating custom column from multiple tables with condition

[lakshmikanthk]

I am trying to create custom column, trying to populate vlaues from two diffrent tables based on condition.

Table 1   to Table 2  relations ship created(active) on workflowid

Table     to Table 3  relation ship created but not active on workflow id

Table 1

Workflowid	Monitor	New Colum
1	true
2	FALSE
3	true
4	FALSE

Table 2

Workflowid	MP ID
1	12
3	14

Table 3

Workflowid	MP ID
2	13
4	15

Expecting out put 

Workflowid	MP ID	New Colum
1	true	12
2	FALSE	13
3	true	14
4	FALSE	15

[dilumd · Accepted solution]

how about appending your table 2 and 3 together first and then do a relation between appended table and table no1?

[Ashish_Mathur · Accepted solution]

Hi,

The best solution has been suggested to you by @dilumd.  If you do not wish to follow that solution, here is a calculated formula you can write in Table1

=if(ISBLANK(RELATED(Table2[MD ID])),LOOKUPVALUE(Table3[MD ID],Table3[Workflowid],[Workflowid]),RELATED(Table2[MD ID]))

For this formula to work, there does not need to be any relationship between Table1 and Table3 - the LOOKUPVALUE() function does not require any relationship - active or not.

Hope this helps.

[dilumd · Accepted solution]

how about appending your table 2 and 3 together first and then do a relation between appended table and table no1?