Data model : how to manage / transform a N - N relationships between 2 tables

[jsteffe]

Hello,
I get the model below :

How can I avoid the relationship many to many between Fertilizer and FertilizerType ?
Should I structure my model differently ?

Thanks for your answers,

Jérôme

[jsteffe]

Thanks for your 2 answers @stefani_vileva and @Pragati11 ...
If I apply what I read, I should add a bridge table .

I get 2 choices : I can use the CROSSFILTER in my measure when necessary ot I can put a both cross filter direction on my relationships like the model below :



I have 2 questions :

1- Did I correctly understand ? Is my new model correct ?

2- what are the differences between using CROSSFILTER in measures and putting a " both cross filter direction on my relationships " ? Is it only a performance difference ?

Thanks again for your help,
Jérôme

[stefani_vileva]

Hello @jsteffe ,

Yes, your new model is correct. About the crossfilter I am not so sure, but you can use the documentation about it.

Kind regards,

Stefani

[stefani_vileva]

Hello @jsteffe,

It is very unusuall in the table Fertilizer to have two or more Fertilizers with the same ID, usually the ID needs to be unique for every fertilizer that you have. If you want to split the M:N relationship, you should create another table that would have both the IDs from the Fertilizers and TypeFertilizer tables, and then try to connect it to Fertilizer and TypeFertilizer.

Kind regards,

Stefani

[Pragati11 · Accepted solution]

HI @jsteffe ,

There are suggested ways to handle this cardinality like bringing in a bridge table. See articles below:

https://www.burningsuit.co.uk/dealing-with-many-to-many-relationships/

https://www.seerinteractive.com/blog/join-many-many-power-bi/