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

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
v-yulgu-msft
Microsoft
Microsoft

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.

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

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.

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.

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


 

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.


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


 

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?

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

You did not get error because you are not listing activities but summarizing them. Change "Activity ID" from "Count" to "Don't summarize" and you have the problem.

 

I begin to think it's the issue with the visual not understanding the relation properly.

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

Well, I guess that's the solution I will use, although I would prefer to just use the relation 😕

 

Thank you, gualvaq.

hi @mzrkwcz

 

Sometimes that is often the easiest and most robust solution instead of spending a lot of time trying something else out!





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

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors