Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
PBI_learn_NK
Frequent Visitor

Issue with joining tables - semantic model as data source

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!
5 REPLIES 5
WishAskedSooner
Resolver II
Resolver II

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.

PBI_learn_NK
Frequent Visitor

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
Super User
Super User

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!