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

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

Trudgeon — Tue, 21 Sep 2021 22:25:32 GMT

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.

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

ponnusamy — Thu, 23 Sep 2021 02:29:43 GMT

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.

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

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

Trudgeon — Fri, 24 Sep 2021 15:45:32 GMT

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.

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.

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

ponnusamy — Fri, 24 Sep 2021 21:57:45 GMT

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.

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

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

Trudgeon — Wed, 29 Sep 2021 21:21:27 GMT

The solution can be found here:

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

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.

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

mellieb — Thu, 02 Jun 2022 17:38:14 GMT

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.

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

mellieb — Thu, 02 Jun 2022 17:39:31 GMT

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.