Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
Hi @datavis,
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
Hi @datavis,
Could you please share your pbix or sample data to me?
Regards,
Frank
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.
Hi @datavis,
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
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.
Finally got the relationship squared away and the string you gave me worked. Thank you so much!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |