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.
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/
Thanks for the response mow700
What you're saying makes sense, but my Usergroups table doesn't contain user records. The Usergroups table contains the usergroup ID and the actual names of the usergroups - there are no duplicates in this table with the exception of the usergroup ID because the 1000+ users can fall into one of the 13 categories. Userlist does not have duplicates rows as it contains all of the unique person IDs for the 1000+ users. Thanks for the link to the page, I skimmed it briefly and it looks like it might be the key to figuring this out. If you have anymore suggestions, please don't hesitate to let me know!
I see now. Does the [Userlist] table have duplicate [Person ID] values?
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.