Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Add column based on two parameters

Hello,   I would like to add a column to the table below (which in reality has thousands or rows and over 20 columns):   Date    |    Machine 6/1      |     EXA11 6/1      |     EXA11 6/1     ...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    There are at least two ways of doing this one.

     

    1.  Add a column in both tables that is the concatenate the Date and Machine, make a relationship between them, and use RELATED() to bring the Goal # to the other table (or better yet just write a measure that gets the Goal # for your calculation)

     

    2. LOOKUPVALUE will search multiple columns.  In your case, something like this in your calculated column should do it

     

    Goal = LOOKUPVALUE(Goal[Goal], Goal[Date], Table[Date], Goal[Machine], Table[Machine], 0) // 0 is the result to return if no value found

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

There are at least two ways of doing this one.

 

1.  Add a column in both tables that is the concatenate the Date and Machine, make a relationship between them, and use RELATED() to bring the Goal # to the other table (or better yet just write a measure that gets the Goal # for your calculation)

 

2. LOOKUPVALUE will search multiple columns.  In your case, something like this in your calculated column should do it

 

Goal = LOOKUPVALUE(Goal[Goal], Goal[Date], Table[Date], Goal[Machine], Table[Machine], 0) // 0 is the result to return if no value found

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Anonymous's avatar
Anonymous
Not applicable
6 years ago

It worked ! Thanks.