Forum Discussion

sk007's avatar
sk007
Regular Visitor
4 years ago
Solved

Switch statement for columns from different tables

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's avatar
    Anonymous
    4 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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)

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.