Power BI Paginated Reports Tutorial - Join data from different datasets in a report table

I often get questions around how can I join data from two Power BI datasets in a paginated report. This walks you through using the Lookup function in Power BI Report Builder, including how the expression is structured and why you should read it "right to left".

[KlasGramstrup]

so nice that you're back. Looking forward to seeing even more of your videos.

[Serraomomma]

After searching and searching for a tutorial such as this, I was skeptical when I found this tutorial! But you NAILED it! I appreciate the time and effort it took for you to make this happen. As a result, I was able to take, a two-day search and make it happen in 5 minutes! You rock! I do have a question though. I would like the result of my vlookup to live in a particular location on the canvas and depending on where i place it, I receive a different result. How do I control it so it does not show repeating values, and does not live on the "Total" line of an unrelated group?

[BradleyLarsen-CGI]

Can the Lookup return a multi-row array? For example, Employee # + Payroll data on the main dataset, then look up Benefit Deductions as an array that can have many rows. Problem being that we have multiple published semantic models with various employee & paycheck info in the Paycheck model, but Benefits are in a different model (ie: data source) that lacks some of the Paycheck/Employee info we'd like on the report. I could write an SQL query (albeit, higher complexity & technical debt to understand underlying DB structure) or create & publish a custom dataset (model) that joins them, but it would be nice to have it self-contained within the Paginated Report. Thanks!

[dalitgilberg]

Thank you for this. My problem is that the value I am trying to fetch from the other dataset, is an aggregation (count distinct), and I get an error that "aggregate functions cannot be used for the destination or result Expression parameter of a lookup function". Is there another way to fetch such a value into my tablix? thank you

[eziola]

Hey Chris, are you no longer the PPM for Paginated Reports? If not, where can we get more of these tutorial videos?

[Serraomomma]

Once I create the table and add my values, how do I change the dataset?

[HereticHulk]

How did you get your dataset "Stuffyname" as column? That doesn't work for me.

[joaoresende]

Cfinlan, let me ask you. I have a individual report for persons. Each report have 5 pages and when generate this i get all pages. I need to generate 800 PDF's files from 800 peoples. How can i do it? thank u

[NaveenKumar-rsoo]

Is there any possibility of converting SQL Query into DAX?
I got a task to migrate SQL queries to DAX but Query Designer is unable to detect DAX and outputs syntax errors.
Connection type - Microsoft SQL Server

[jdawg]

Any way to actually merge two datasets?

[ramyakeerthipathakamuri]

How to use fields in one data set as filter expression in another data set?