cancel
Showing results for 
Search instead for 
Did you mean: 
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
Frequent Visitor

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.