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.