Forum Discussion
Connecting Two Tables w/ Lookup
Hi Anonymous,
I am assuming that there can be more than one row with the same Usergroup ID in Learning plans and you essentially want to join Learning Plans and Userlsist on the Usergroup ID. The relationships aren't much help in getting to a solution here but there are other ways. I am not sure if this is really the best solution, but if you want is to create a new table with a join between Learning Plans and Userlist, something like this could be a starting point. Click "New Table" and enter the following (or your edit of the same)
NewTable = VAR LPT =
SELECTCOLUMNS('Learning Plans',
"UG",'Learning Plans'[Usergroup ID],
"Learning Plan",'Learning Plans'[Learning Plan]) RETURN FILTER(CROSSJOIN(Userlist,LPT), Userlist[Usergroup ID] = [UG])IN SELECTCOLUMNS you identify the table you want to select columns from followed by a name for the column and a definition. The reason this is necessary is because the column we want to join on have the same name ([Usergroup ID] in both tables, so all we are doing here is renaming that column. You should include all columns from Learning Plan that you want in the result.
The FILTER statement simply selects all rows in the table resulting from the crossjoin between the SELECTCOLUMNS and the Userlist where the Usergroup ID columns agree.
As I said, not particularly elegant but it gets the job done. I hope somebody can come up with something better.
One workaround might be to add some calculated columns to your Usergroups table to co-locate all your data in one place. Since the tables are related, you can use something like:
First Name = RELATED('Userlist'[First Name])
Class Name = RELATED('Learning Plans'[Class Name])
If your Usergroups table contains the values you want to use in your visual you should see the expected result.
Thanks for your response - mow700
I tried to create a new column using the syntax you provided, but it didn't work as the related() function wouldn't even recognize the first name column - I also tried relatedtable(), but ran into a similar issue. I'm not sure if this approach will get me the desired result.
The Usergroups table is a reference table that is comprised of 2 columns, 'Usergroup ID' and 'Usergroup'. There are only 13 rows in this table and the plan is to map them to the other 2 tables using the Usergroup ID.
Hi Anonymous,
it is not entirely clear what it is you want to achieve here. UserGroups table has a one to many relationship to both UserList and Learning Plans, which implies there is a many-to-many (implied) relationship between users and learning plans. What is it exactly you are trying to tie together?
It would help if you could give an example of what visual you are trying to create on the dashboard using this data model.
Thanks for the response erik_tarnvik
The ultimate goal here is to create a dashboard that will take use the tables presented on this thread, along with other tables containing training data from users (i.e., class start dates, class completion dates, class locations, class grades, etc.) to report on training for users. I would like to tie together the Userlist and the learning plan tables by user group ID. The userlist contains a foreign key in the usergroup ID field, but there is also another foreign key in the Person ID field as each person who is in training has their own unique person id - this ID is not duplicated.
My strategy here is to break this project into bite size chunks and test at each small milestone rather than get to the end and have something break with no idea where the break is located. For your reference, each user is assigned to one usergroup; each usergroup is assigned to one learning plan; each learning plan has a set of classes that correspond to it. With the data I have provided to you all, I would just like to see a single table with a user's first name, last name, class names and learning plan.
I hope this provides a little more context. If not, please let me know.
Ahh, then I might see the problem. According to your screenshot you have a many to one relationship against your [Userlist] and [Usergroups] tables. From your description of the desired result, I would expect just the opposite relationship. One User in the [Userlist] table to many User records in the [Usergroups] table, related by User keys. Does [Userlist] contain duplicate rows for a User? Does your [Learning Plans] table contain duplicate rows for groups? If either of these are true, you may need to rethink how your data is structured and consider transforming it prior to making the relationships. I've recently used this article to better understand how my data should be structured to make the most out of my models:
http://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/
If the RELATED() function isn't working, you likely have a problem with the relationships. What error are you receiving? If intellisense can't find one of the columns you expect to be related you have a problem. Basically you have created a valid relationship, but the keys never actually match.
You may need to insure the datatypes of both columns in your lookup table match the source tables. For example numbers interpreted as text might not produce the expected result, but allow the creation of relationship anyway.