cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
datavis Member
Member

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?

Relationships.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Create column from a different table

Hi @datavis,

 

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

re.PNG

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.

 

Capture.PNG

 

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Community Support Team
Community Support Team

Re: Create column from a different table

Hi @datavis,

 

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

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
datavis Member
Member

Re: Create column from a different table

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.

Combined-ActivePositions.PNG     DelinquentTraining.PNG     Employee ID.PNG  

Community Support Team
Community Support Team

Re: Create column from a different table

Hi @datavis,

 

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

re.PNG

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.

 

Capture.PNG

 

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
datavis Member
Member

Re: Create column from a different table

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.

 

Relationships.PNG

datavis Member
Member

Re: Create column from a different table

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