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

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

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