Create column from a different table

[Anonymous]

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?

[v-frfei-msft]

Hi @Anonymous,

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

Regards,

Frank

[Anonymous]

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 · Accepted solution]

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]

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]

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