Solved: Connecting Two Tables w/ Lookup - Page 2

Anonymous · ‎10-11-2017

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!

erik_tarnvik · ‎10-11-2017

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.

View solution in original post

Connecting Two Tables w/ Lookup

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly