Forum Discussion
Helper IIICan we retain table relationships when using 'Add Power BI Dataset Connection' in Report Builder?
The solution can be found here:
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.
Solution SupplierTrudgeon : 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.
Trudgeonponnusamy 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.
- ponnusamy
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.