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

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

Hi Anonymous,

 

Could you please share your pbix or sample data to me?

 

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.