Relationship between tables in Power BI Report Builder

[ola1996]

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?

[IanBates]

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

[Laurence21]

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.

[samatya]

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!

[Laurence21]

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?

[Vijg00]

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.

[Linoge]

Thanks, fought with this for a while, your input does the trick

[GOI_01]

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

[SunilKumar123]

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

[Oscar_Mtz_V]

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]

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 · Accepted solution]

@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.

[a1b1c1]

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]

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?

[a1b1c1]

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]

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...

[a1b1c1]

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

[ola1996]

Hi all, 

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