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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Trudgeon
Helper III
Helper III

Can we retain table relationships when using 'Add Power BI Dataset Connection' in Report Builder?

I'm trying to recreate a Power BI table report as a paginated report.  In Power BI Report Builder, I was able to connect to the Power BI dataset by right-clicking on Data Sources and selecting 'Add Power BI Dataset Connection'.  However, problems arose when adding the dataset.  In Query Designer, I can see all 10+ of the tables in my Power BI dataset.  I was able to drag & drop the necessary fields into the workspace, but when I executed the query, I had a ton of duplication issues.  It was obvious the tables were no longer joined by related fields.

 

Is there any way to use the 'Add Power BI Dataset Connection' in Power BI Report Builder for Power BI datasets with multiple tables AND retain the relationships?  Power BI Report Builder is pretty cumbersome and it would be amazing to add Power BI datasets with relational integrity.  That would make life so much easier.

1 ACCEPTED SOLUTION
Trudgeon
Helper III
Helper III

The solution can be found here:

https://community.powerbi.com/t5/Desktop/Relationship-between-tables-in-Power-BI-Report-Builder/m-p/...

 

In summary, when you connect to power bi dataset from power bi desktop/report builder, the connection mode is set as live connection which means you can't do any modification on the data model(e.g.: table relationship) so you can't see the relationship.  However, you can use the RELATED function to get field from dimension tables into a single fact table.  Once all of the required fields are in one table, you can build the query in Power BI Report Builder from the single source.  It's certainly a pain, but it works.

View solution in original post

6 REPLIES 6
mellieb
Helper I
Helper I

I agree.  Why doesn't report builder retain the relationships/joins when connecting to your dataset/data model?  You have to create the relationships twice?  I feel like this is a design flaw.  

Trudgeon
Helper III
Helper III

The solution can be found here:

https://community.powerbi.com/t5/Desktop/Relationship-between-tables-in-Power-BI-Report-Builder/m-p/...

 

In summary, when you connect to power bi dataset from power bi desktop/report builder, the connection mode is set as live connection which means you can't do any modification on the data model(e.g.: table relationship) so you can't see the relationship.  However, you can use the RELATED function to get field from dimension tables into a single fact table.  Once all of the required fields are in one table, you can build the query in Power BI Report Builder from the single source.  It's certainly a pain, but it works.

What if you have an extremely large dataset that can't handle one single table?  I don't understand why you have to create the relationships twice.  Seems to be a design flaw to me.

ponnusamy
Solution Supplier
Solution Supplier

@Trudgeon : I did the following and able to get the relationship of the tables from datasest. I am not sure why its not using proper relationship when I don't have measures pulled in as part of query designer.  Try the following and you can see the relationship from dataset is retained in the query.

 

ponnusamy_0-1632364172367.png

 

If this post helps, then please consider Accepting it as the solution, Give Kudos to motivate the contributors.

@ponnusamy Thank you for the response.  I'm confused because I feel like I'm doing the same thing you did.  I brought in columns from different tables (denoted by colors in image), but I'm getting obvious duplications from a lack of relationships.

 

QueryDesigner.PNG

 

Looking at the resulting query, there are no joins.  Since this was built from an imported PowerBI dataset, I would've expected that the table joins from BI would be maintained Report Builder...but it appears not to be the case.  I suppose I can manually join the tables in the code, but it seems like there might be a simpler way.

Query.PNG

 

@Trudgeon : I see one difference, I am using measures from the dataset and you are not. However, without using a predefined measure I would also expect PowerBI to maintiain the relationship defined in the dataset (Model). I am assuming that it could be unfinished feature or bug in the tool. Try to bring in one measure from the dataset to the query.

 

ponnusamy_2-1632520650310.png

 

ponnusamy_1-1632520458924.png

 

If this post helps, then please consider Accepting it as the solution, Give Kudos to motivate the contributors.

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.