Issue with joining tables - semantic model as data source

[PBI_learn_NK]

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 I'm trying to achieve:

• An inner join between the two tables on ContactId
• A column for FirstName, LastName, and a concatenated ContactName (i.e., FirstName + ' ' + LastName).
  
  I tried using NATURALINNERJOIN, but it resulted in the error:
  No common join columns detected. The join function NATURALINNERJOIN requires at least one common join column.
  
  
  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?
  
  Thanks in advance for your help!

[WishAskedSooner]

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?"

[PBI_learn_NK]

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.

[PBI_learn_NK]

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!

[ajohnso2]

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

[PBI_learn_NK]

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.