Switch statement for columns from different tables

[sk007]

I have two tables like this: 

Table1

 

Table2

I want to create a new column in either of the table with condition like this: 

If pay is Table1[pay] = "A" or Table1[pay] = "B" then, NewDate = Table2[n_date]

If pay is Table1[pay] = "C" or Table1[pay] = "D" then, NewDate = Table2[p_date]

How can i do this using Switch or other DAX query?

[Anonymous · Accepted solution]

Hi @sk007 ,

If you want to calculate between Table1 and Table2, there should be a keycolumn to relate two tables. Or we couldn't determind which row each pay will be related to.

I agree with amitchandak, you can refer to his video to add an [Index] column in two tables in Power Query Editor. Please make sure you have sort two tables as you want before you add an [Index] column.

Relationship:

Then your calculation will be easy.

Calculated Column:

NewDate =
SWITCH (
    TRUE (),
    Table1[pay] IN { "A", "B" }, RELATED ( Table2[n_date] ),
    Table1[pay] IN { "C", "D" }, RELATED ( Table2[p_date] )
)

 Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@sk007 , How will the row will get decided? both of them need have pay or add an index column in the power query

Power Query- Index Column: https://youtu.be/NS4esnCDqVw

Then have a column in Table on

COlumn =

Var _1 = maxx(filter(Table2, Table1[pay] =Table2[Pay]) ,[n_date]) // use index in place of pay if needed

Var _2 = maxx(filter(Table2, Table1[pay] =Table2[Pay]) ,[p_date])// use index in place of pay if needed

return

if([Pay] in {"A", "B"} , _1,_2)