relation from powerbi desktop model not used in report builder

[VV24]

Dear all,

I have made two tables in my powerbi data model and

table 1:

ID	Company Name
1	A
2	B

 table 2:

ID	Product
1	AA
2	BB
1	CC

there is a relationship 

between de IDs. so in powerbi I get a table returned like this:

Company	Product
A	AA
A	CC
B	BB

But in the query designer from the report builder this is returned:

Company	Product
A	AA
B	AA
A	BB
B	BB
A	CC
B	CC

Which from a powerbi desktop side looks like there is no relation anymore between these tables. How will this properly work? Both fields are dimensions.

Best

[parry2k]

@VV24 yes will find out but I Power BI desktop is smart enough to generate the query and add a measure to get the result whereas the report builder is not. but to your point, yes, if it is one to many relationships, it should just list respective records from the child table. 

[parry2k]

@VV24 yes, it doesn't matter, it will still return a cartesian result even if there is a relationship. As suggested previously, drop columns in the table visual in Power BI desktop and get the query from the performance analyzer, and if you look at the query, it does add countrows measure internally and that's why it works in Power BI desktop, not in Report builder. You can take this query and use it in report builder and everything will work. 

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[VV24]

@parry2k thanks, will do that, still keeps my sitting with the question why?? haha, there must be some logic or explanation why this is not working. Solving is done now 😉

[parry2k]

@VV24 exactly. hmmm I think the first few minutes when I try to show how to list product and customers and it gives the cartesian result and how to overcome this.

One thing you can do is put this in Power BI in a table, thru the performance analyzer grab the DAX query, and then use that query in report builder. That will do it.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[VV24]

@parry2k 

the difference between your video and my example is that you will never get the result (in power bi desktop) between customer and product because there is no relation between these tables due to the filter directions. In my example I do have a direct relation between these two tables and still this does not work. I dont have a cartesian result in Power BI desktop but I do have this in the Report Builder

[VV24]

@parry2k 

Why is there a difference then? I saw it worked with a measure, is there always some kind of aggregation needed?

what if the result shoudl be two dimensions? Do you always need to add an aggregation?

any specific time you talk about this in your video?

[parry2k · Accepted solution]

@VV24 you need to add a measure to make it work, I recently talked about this in a meetup, you can check the video here.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡