cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mzrkwcz Helper I
Helper I

Reporting with many to one to many relationships

Power BI says "Can't determine relationships between fields", when I try to create visual based on 3 tables related many to one to many. What is the problem and how to solve it?

My tables(columns) are:
Activities (Activity ID, Deal ID)
Deals (Deal ID)
ProductsInDeals(Deal ID, Product ID)

My relationships are:
Activities[Deal ID] many to one Deals[Deal ID]
ProductsInDeals[Deal ID] many to one Deals[Deal ID]

 

Now, I get the error message when I try to use table visual with following fields:
Activies[Activity ID], Deals[Deal ID], ProductsInDeals[Product ID]

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Reporting with many to one to many relationships

Hi @mzrkwcz

 

Ok that makes sense then because it is not aggregating the value but wanting to show the ActivityID

 

In that instance I would potentially merge the data together in the Query Editor





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog

View solution in original post

13 REPLIES 13
Microsoft
Microsoft

Re: Reporting with many to one to many relationships

Hi @mzrkwcz,

 

Please check whether you have created relationships between these three tables. If not, it will prompt above error when referring to 3 tables in a single visualization.

1.PNG

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mzrkwcz Helper I
Helper I

Re: Reporting with many to one to many relationships

Yes, it looks exactly as on your screenshot.

 

I want to use the table visual that is the important detail.

 

I just discovered that:

1) when I have table visual with Activity ID, Deal ID and Product ID, Power BI sees a problem with relations;

2) when I add one more field to the table visual - a measure of number of unique Activity ID values, problem disappears for Power BI.

 

I just don't understand why this happens...

Microsoft
Microsoft

Re: Reporting with many to one to many relationships

Hi @mzrkwcz,

 

What is the data type of these three columns Activity ID, Deal ID and Product ID?

 

If you only add two columns into table visual, for example, Activies[Activity ID], ProductsInDeals[Product ID], will it prompt error?

 

Since I could not reproduce your issue, if possible, please share your pbix file so that I can test for you.

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mzrkwcz Helper I
Helper I

Re: Reporting with many to one to many relationships

All columns are "Whole number" type.

 

Here is the pbix: https://www.dropbox.com/s/7868b7jkzjp2gtz/RelationshipsProblem.pbix

 

Just add/remove measure "Number of Actions" to see what I am talking about.

Super User IV
Super User IV

Re: Reporting with many to one to many relationships

Hi there

 

Possibly change the Cross Filter Direction between the Activities and Deals table to single.

 

Power BI - DELETE.png

 

It should then work correctly?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog
mzrkwcz Helper I
Helper I

Re: Reporting with many to one to many relationships


 

Possibly change the Cross Filter Direction between the Activities and Deals table to single.

 


That is the first thing I tried. It does not change the situation.


Super User IV
Super User IV

Re: Reporting with many to one to many relationships

Hi there

 

I changed it to Single and I was able to put in the ActivityID?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog
mzrkwcz Helper I
Helper I

Re: Reporting with many to one to many relationships


 

I changed it to Single and I was able to put in the ActivityID?


 

Hm, strange, I still see:

2017-04-13_0647.png

Can you share the file, please?

Super User IV
Super User IV

Re: Reporting with many to one to many relationships

Hi @mzrkwcz

 

Here is a link to the file

 

https://1drv.ms/u/s!Apxn-69XhcAmhqdoNldmrIzjjzgC_w





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors