Reporting with many to one to many relationships

[mzrkwcz]

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]

[v-yulgu-msft]

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.

Regards,
Yuliana Gu

[mzrkwcz]

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

[v-yulgu-msft]

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

[mzrkwcz]

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.

[GilbertQ]

Hi there

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

It should then work correctly?

[mzrkwcz]

---

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.

---

[GilbertQ]

Hi there

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

[mzrkwcz]

---

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

---

Hm, strange, I still see:

Can you share the file, please?

[GilbertQ]

Hi @mzrkwcz

Here is a link to the file

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

[mzrkwcz]

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.

[GilbertQ · 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

[mzrkwcz]

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

Thank you, gualvaq.

[GilbertQ]

hi @mzrkwcz

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