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 in the table Combined-ActivePositions[Training Compliance].  Can I use Combined-ActivePositions[EmplID] to lookup the DelinquentTraining[Course with Name] and get the Combined-ActivePositions[Course Name] using the relationships set up below? I tried a LOOKUPVALUE but I think I need to setup a relationship between Combined-ActivePositions and DelinquentTraining tables first. How do I do that?

Need Help

5 Replies

  • 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