Forum Discussion
Advocate IIMerged columns in Report Builder
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:
| ID | First_name | Bill_amt | Cash_amt | Loan_amt |
| 12345 | John | $ 234.00 | $ 230.00 | $ 67029.00 |
In the table visual, you can do Lookup or LookupSet. Check these links:
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.
3 Replies
sevenhillsSuper User
In the table visual, you can do Lookup or LookupSet. Check these links:
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)
