N to N relation not detected by power BI (in Tabular and in power BI)

[Fvrdp]

Hello Everyone,

I search around but do not find anything like following case :

On power BI desktop june2021 I have this model : all links are on id people, 1 to N, bidirectional, all data are imported

My goal is to show in the same table DDTagcategory and User category each one from fact tables.

Instead I get such message :

This is a classic case of N to N relationship. and still not work so I try to force powerBI to undestand the case and draw a direct N to N between the 2 tables.

This works fine. It look like powerBI does not understand the N to N context. This solution may be a dirty workaround.

More strange, If i roll back to the first model (that does not work) and I update the agregation behavior :

It works also but the result is not really what I expect : I don't want to summarize.

Last thing,  SQL source tables comes from a data warehouse on which I build a tabular model in Visual studio that use exactly the same model in my example :

This tabular model works perfectly in Excel querying and my example ( DDTagcategory and User category.) is rendering flawless.

But if I try a direct query on this tabular model with powerBI, it still does not work for same reasons since powerBI import relationship.

So my question is :

Is there any adjustment I may have forgot that could help powerBI to understand that 2 fact tables with a dimension in the middle is a N to N relationship ?

Thank you in advance

[v-janeyg-msft · Accepted solution]

Hi, @Fvrdp 

Have you tried to delete the many-to-many relationship, and add the common field between the slave table and the two tables and other fields you want? 

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,

Community Support Team _ Janey

[Fvrdp]

Hi Janey,

Thank you for your help. Yes I had try such thing with several relationship combinaison and I finally locate the pain point.

To find it, I had simplified the schema to match the test case only. It looks like this :

This is a typical N to N relation between Fact1 and 2. This occurs frequently when several star schemas works together. If I display a table with state and status field i still have a relational error.

Finally, this will be surprisingly fixed by adding an agregated field in the table (!)

So the answer to my question in first post was : you're forced to add an agregated field to make it works.

I was quite surprised since this behavior is not the same in tabular model : this one does not need a calculated value :

Then, the error raised from powerBI is not relevant in this case :  it is not a relation problem but a report building issue. I'd learn something today !

Problem solved, thank you !

Best regards.

[Fvrdp]

Hi Daxer, 

First of all thank you for your quick answer and your time.

However this does not answer the question. This was asked for the 3 tables model not for the visual studio model, this one works correctly in tabular AS. I do not understand exactly the problem with the model in PowerBI: this is a "classic" BI constellation use case, but maybe this is the weak point and I missed it.

As Microsoft says that Tabular AS with soon been a part of power BI  (https://powerbi.microsoft.com/en-us/blog/power-bi-as-a-superset-of-azure-analysis-services/) and the inner engine is still the same, this seems a bit strange that a model does not have the same behavior in powerBI than in tabular AS.

Now, I fully assume to not be aware of all the tricks of PowerBI modeling, and this is why I gonna still searching/learning  on other community which will have some more constructive answers, sorry for the inconvenience 🙂

Best regards.

[Anonymous]

@Fvrdp 

You don't understand the golden advice I've given you. That's sad. Just remember me when you'll be getting wrong figures all over the place and you won't know where they come from...

[Anonymous]

OK. I'm not gonna dive into the details as there are too many of them I'd have to explain but I'm gonna tell you something and give you a golden piece of advice or a piece of golden advice, whatever you call this. Do not build such monsters or you'll be facing a total rebuild in a few weeks or months. The way you do it is totally and utterly against Best Practices of dimensional modeling in PBI and will get you into so much trouble in no time that it'll make you cry. Please create a good model according to Best Practices. If someone came to me and showed me this, they would be looking for a new job the next day.

I know I sound a bit harsh... but you can take my word for that: abandon this bad model. Please take time to learn how to do it properly. If you don't do it now, you'll have to do it later when you've forgotten all the nitty-gritty details of how this monster works. Remember the saying: "There's never time to make it right but there's always time to make it twice."