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.
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?
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!