Merged columns in Report Builder

[rnola16]

Moving from Business Obj to PBI, where in the former BI application there exists a feature 'Merged Dimesnion' where we merge fields from multiple queries. In Report Builder if I were to merge fields from multiple ( 3 or more) data sets how do I do it ?

These data sets have enqual number of columns between them and no direct co-relation if we are do a join bw them.

Eg: 

SELECT d1.ID, d1.first_name, d1.Bill_amt FROM Dataset1 d1

SELECT d2.ID, d2.first_name, d2.cash_amt FROM Dataset2 d2

SELECT d3.ID, d3.first_name, d3.loan_amt FROM Dataset3 d3

Need out put as:

ID	First_name	Bill_amt	Cash_amt	Loan_amt
12345	John	$ 234.00	$ 230.00	$ 67029.00

Does Lookup() func work for 3 or more datasets ?

Thanks.

[sevenhills · Accepted solution]

In the table visual, you can do Lookup or LookupSet. Check these links: 

https://www.mssqltips.com/sqlservertip/2141/sql-server-reporting-services-lookup-lookupset-and-multi...

https://learn.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-look...

https://stackoverflow.com/questions/48807794/lookup-on-2-datasets-in-ssrs

FYI: Paginated Reports ~ SSRS ~ PBIRS Report Builder ~ PBI Service Report Builder should behave the same way. 

[lbendlin]

Isn't the ID field the join column?

In general the thing you are trying to do is more of an append rather than a merge, at least in Power Query.  In DAX this is more something you would do (crudely) via NATURALINNERJOIN

DEFINE	
var d1 = SELECTCOLUMNS(dataset1,"ID",[ID] & "","First Name",[First_name],"Bill_amt",[Bill_amt])
var d2 = SELECTCOLUMNS(dataset2,"ID",[ID] & "","Cash_amt",[Cash_amt])
var d3 = SELECTCOLUMNS(dataset3,"ID",[ID] & "","Loan_amt",[Loan_amt])
Evaluate NATURALINNERJOIN(NATURALINNERJOIN(d1,d2),d3)

[rnola16]

Thanks @lbendlin but I'm looking to introduce this in Power BI Report Builder ( paginated reports).