Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi everyone,
I’m in the process of migrating an SSRS (RDL) paginated report to Power BI Paginated Reports( PBI Report Builder ). I’ve set up a semantic model (created in PBI Desktop and published to PBI WS) as the data source and am currently trying to rebuild the dataset queries using DAX in the Power BI Report Builder.
I need to join two tables, SALES and CONTACT on ContactId, and the dataset query should display columns from both tables, including a concatenated full name (FirstName + LastName). However, I’m encountering an issue where the join results in a Cartesian product, instead of an inner join.
EVALUATE
SUMMARIZECOLUMNS(
SALES[ContactId],
CONTACT[FirstName],
CONTACT[LastName],
TREATAS(VALUES(SALES[ContactId]), CONTACT[ContactId]) )
What is the correct way to perform an inner join on ContactId between these two tables in a DAX query within Power BI Paginated Report BuilderHow can I ensure that the concatenation for ContactName works correctly?
Have you tried to join the tables using Power Query instead? When it comes to join operations, yes, you can do it using DAX, but the question you need to really ask before doing so is, "Should I?"
Thank you for your suggestion!
In this case, I’m not using Power Query for the joins because the data model is already designed for direct querying in Power BI, and the focus is on ensuring the paginated report uses the semantic model efficiently. The joins need to reflect specific cardinalities and filter behavior, which is managed more flexibly within DAX at runtime in this scenario.
However, I can create a separate model just for this report to eliminate the join issue.
That said, I’m still seeking help with how to create a column that combines FirstName, LastName, and a concatenated ContactName (i.e., FirstName + ' ' + LastName) in the paginated report DAX query. Any guidance on that would be greatly appreciated!
Your tables should already be joined in your PBI model? Im unsure why you are trying to do it in paginated reports?
If you have a sample model to share it would be useful
Thank you for your response!
The tables aren't joined in the Power BI semantic model, as there are other considerations around cardinality and business logic that will affect how the relationships are defined. For now, I'm working with the dataset directly in the paginated report.
Unfortunately, I can’t share the model from my office machine due to data privacy policies, but I’m happy to clarify any specifics if needed.
User | Count |
---|---|
121 | |
72 | |
71 | |
57 | |
50 |
User | Count |
---|---|
167 | |
83 | |
68 | |
65 | |
55 |