Connecting Two Tables w/ Lookup

[Anonymous]

Hi Everyone,

This is my first time posting to this community, so forgive me if I'm not using proper etiquette or providing enough context; I will offer more context as requested, just go easy on me if I'm doing it wrong.

I'm a relatively new Power BI user (began working with Power BI at the beginning of September) and I'm experiencing issues connecting (joining) two tables together. The desired result is for me to be able to go to the canvas and pull the first name and last name from the Userlist table and also pull the Learning Plan and class name from the Learning Plans table.

I recognize that I don't have a direct relationship b/w these two tables, but that's why I'm reaching out to you all here. I know there needs to be a connection (column/foreign key), I'm just not sure what that looks like. The Usergroups table that the other two tables are connected to contains a unique list of the usergroup IDs (primary key) and the actual user groups.

Any and all suggestions are welcome and appreciated. I'm a willing learner, so please share the knowledge and expertise you have. Thanks in advance! 

[mow700]

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.

[Anonymous]

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.

[mow700]

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.

[erik_tarnvik]

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.

[Anonymous]

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.

[mow700]

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/

[Anonymous]

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!

[mow700]

I see now.  Does the [Userlist] table have duplicate [Person ID] values?

[Anonymous]

Thanks for the response @mow700

The Userlist table does not have duplicate Person ID values.

[erik_tarnvik]

I should also add that you have the opportunity to reshape your data using Power Query if your input data does not come in a form most suitable for the way you want to model the data. It is very powerful.

[Anonymous]

Thanks for the response @erik_tarnvik

A few things have happened since I last responded:

After reading the article that @mow700, reading your suggestion, and applying your code snippet I have a much better understanding of how to model my data and I have a working solution! 

In your suggestion with the code snippet, you use the word "join" -after spending so much time with the problem, I essentially forgot what my end goal was- and thought about trying to merge queries. So I clicked "edit queries" -> "merge queries" (just to be safe to put the merge in its own table) -> selected Userlist as the top table and Learning Plans as the bottom table and performed a Left Outer join. I compared my results from the merged query to the results from the code snippet that you provided and they seem to be identical.

I think this is the "more elegant" solution you alluded to. Is there any way to verify? If merging the queries proves to be the right way to do it, I want to thank you for placing me on the right path, but I also want to make sure that I give you credit. How would I do so?

[erik_tarnvik]

Hi @Anonymous,

with "more elegant" I was referring specifically to my DAX construct. Often there is more than one way to accomplish things in DAX and sometimes the first solution isn't necessarily the best one.

What struck me about your question was that although it was possible to acomplish what you asked for, you would probably find it easier to work with Power BI if you spent some time figuring out how to structure your data set in Power Query before attacking the analytics with DAX. @mow700 sent you in the right direction for that. 

Happy to be of help! The way to give credit around here is to give "kudos" by pressing the "thumbs up" button next to a post, and to mark a post as a solution to the problem. Much appreciated if you do so, thanks!

[erik_tarnvik · Accepted solution]

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.

[cthurston]

Try making the link between Usergroups and Userlist cross filter then you should have a table that relates First and Last Name to Plan and Class based on the usergroups ID Key.  Also is Usergroups ID a one to many relationship for both tables?

[Anonymous]

Thanks for the response - @cthurston

I currently have the cross filter on the two relationships set to "Both". And yes, Usergroups ID is a one to many relationship for both tables.

Here's my result: