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
ola1996
Helper I
Helper I

Relationship between tables in Power BI Report Builder

Hello, 

I would ask you about relationship in Power BI Report Builder. My case: 

1. I create report with many relationships between tables in Power BI Desktop, my datasource is Dynamics Business Central

3. I published my report into Power BI service 

4. I connect with Power BI service in Power BI Report Builder 

 

When I choose columns from different tables in my dataset in Power BI Report Builder but it's looks like Report Builder doesn't see relationships created in Power BI Desktop. 

Is there any option to solve this problem?

 

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

@ola1996 ,

 

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.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

16 REPLIES 16
IanBates
Frequent Visitor

Is there a newer, more direct solution to this problem?

Report builder does actually see the relationships within a Power BI /dataset that you connect to. When you use the table wizard and pull in various columns from different tables in the model, the wizard generates a DAX query on the whole model, and the relationships between tables work fine.

I think @ola1996 was incorrect when he thought that Report Builder doesn't see the relationships. Admittedly, I thought the same thing when I started using Report Builder.

But try it - pull in a measure from your FACT table as one of your columns and pull in a column from a dimension table.  The measure result will be sliced via the relationship defined in the PBI model.

I gave this a try in several occasions - didn't work for me. What I see in Power BI desktop in a table Vs when I use the same fields in Power BI query Designer - the results are compltely different - everything is proprely sliced and clean in PBI Desktop while the relationships seem to be broke in PBI report builder (instead of a f ew thousand I get a million rows with a query in PBI Report Builder). I am creating views with columns/measures from a FACT & DIm tables. Would be super helpful if it worked!

Hi Samatya,

 

I am certain that the relationships between tables in a Power BI dataset/data model still operate if the model is connected to and queried in Report Builder. I have built a few paginated reports that were dependent on this being the case.

Perhaps use a field from your dimension table as a report parameter, and see the difference filtering on that parameter makes to a measure that performs a calculation on a related FACT table?

I may have found a solution to this problem.

Confusion struck me too as in some cases the tables were joined as designed, and in some cases the joins were ignored.

@Laurence21 is actually right, but the solution needs to be read differently.

 

It is not a matter of "try a measure and some dimensions" but you HAVE TO ADD A MEASURE to your query in Report Builder.

Without measure, the joins are ignored. With measure, everything is fine (even if you don't use the measure at all). Even a simple 'count rows' is enough.

Hope this helps for you all too.

thank you @Vijg00 this was a life saver for me.

@GOI_01 Can you please elaborate on what you did to resolve this. TIA

Oscar_Mtz_V
Kudo Commander
Kudo Commander

The best thing to do in here is to copy/get the DAX query from Power BI into the Report builder. 

https://docs.microsoft.com/en-us/power-bi/paginated-reports/report-builder-shared-datasets#get-the-d...

 

Marian
Frequent Visitor

Hi, this video might help. Use Power Query to do generate the data  table which combines the data you need.

https://www.youtube.com/watch?v=NfoOK4QRkhI&feature=emb_imp_woyt

 

It worked for me

 

v-yuta-msft
Community Support
Community Support

@ola1996 ,

 

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.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

In continuation to the question posed by @ola1996, how can we solve that problem. It seems that the relationships defined in published power bi dataset are not working in Power bi report builder.

Thanks in advance 

Anonymous
Not applicable

Hi everybody.

I do not understand why thgis has been marqued as "solved". The conclusion is that PowerBI Report Builder cannot use the relationships between tables that are defined in PowerBI Desktop and published.

This is a HUGE drawback, as it is one of the VERY useful funcionalities of PowerBI.

Does anyone knows how to solve this? Maybe using a Lookup?

Hi @Anonymous As a workaround, I got the columns from Dimension tables in my fact table using RELATED function. After publishing the dataset and connecting again, I used the fields from the fact table, avoiding the need for a join.
If need be, you can use Power Query to make duplicate tables and perform transformations accordingly so that one (or more) separate tables contain independent information, to avoid the problem at hand.    

Anonymous
Not applicable

Hi @a1b1c1 .

Thanks for the tip. This is what I was about to do, but I find it very frustrating to have to tweak the PowerBI file and move the dimensions to the facts table just because the PowerBI Report Builder tool isn't able to use the already defined relations between tables...

@Anonymous Yes, but since we don't have a proper solution to this as of now, it's the only thing to do.  

Hi all, 

 

How about function "analyse in Excel" are realtionship made in Power BI visible in Excel? 

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.

Top Solution Authors