link three tables by the unique key through Paginated reports (report Builder, SSRS)

[TiffanyCui]

Hi all,

I'm trying to create a full data report by Paginated Report (SSRS).

First, I connect the Oracle database to the desktop creating the layout, then publish it to the online service. The relationship among these three tables can be built automatically by the desktop. For example, when I filter by the order number, it will affect all three tables.

Second, I add the Power BI dataset which I published to the online service to Paginated reports (SSRS).  But The relationships in the Power BI model do not follow through to the Paginated report,  i.e., a data model is not created within Report Builder. When I choose the order number, it can only affect 1 table, the other two tables will not be linked. 

Third, because of the way I set it up, I've tried the SQL using the unique key to connect three tables but it didn't work out.

Please feel free to let me know if you have any thoughts.

Best Regards,

Tiffany

[v-yingjl · Accepted solution]

Hi @TiffanyCui ,

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.

You can refer this viedo to use Power Query to do generate the data  table which combines the data you need.

Power BI Paginated Reports Bonus Episode - Using DAX queries from Power BI Desktop 

Simliar issue: Relationship between tables in Power BI Report Builder 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.