Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Create column from a different table

I need to create a new column called Course Name in the table Combined-ActivePositions that will pull the Course with Name from the DelinquentTraining table for all EmployeeIDs that are Non-compliant...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous,

     

    Based on my test, we can create the relationship between tables like this.

    Then we can create a calculated in table CombinedActivePositions using the formula.

     

    Course Name = CALCULATE(MAX(DelinquentTraining[Course with name]),USERELATIONSHIP(CombinedActivePositions[EmplID],DelinquentTraining[EmplID]))

    Here is the result for your reference.

     

     

    For more details, please check the pbix as attached.

     

    https://www.dropbox.com/s/z8zkf346qrl8et3/Create%20column%20from%20a%20different%20table.pbix?dl=0

     

    Regards,

    Frank

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Frank,

Sorry, I can't share the file so I recreated images to help explain. Hope this helps.

I need a column in CombinedActivePositions table called Course Name to use in a slicer.

I created an EmployeeID table by Append to New combining CombinedActivePositions table and DelinquentTraining table. Then I deleted all columns except the EmplID column.

I need to get the course name for each of the Non-Compliant EmplIDs in DelinquentTraining into the new column in Combined ActivePositions called Course Name. Hope that makes sense. See tables below.

            

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi Anonymous,

 

Based on my test, we can create the relationship between tables like this.

Then we can create a calculated in table CombinedActivePositions using the formula.

 

Course Name = CALCULATE(MAX(DelinquentTraining[Course with name]),USERELATIONSHIP(CombinedActivePositions[EmplID],DelinquentTraining[EmplID]))

Here is the result for your reference.

 

 

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/z8zkf346qrl8et3/Create%20column%20from%20a%20different%20table.pbix?dl=0

 

Regards,

Frank

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thank you for helping. I get a cardinality error - "DelinquentTraining cardinality is set to many. TO end cardinality must always be set to one." I had originally created the EmployeeID table because there are not unique values (Employee IDs) between DelinquentTraining and CombinedActivePositions. Also, to eliminate confusion I removed a couple of unnecessary relationships created before I combined the two employee files into CombinedActivePositions. See revised relationships below. Thank you again for your help.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Finally got the relationship squared away and the string you gave me worked. Thank you so much!!!