Need help with avoiding many to many relationship

[BIlix]

I have a data Model that contains a project table with project entries on a project entry granularity. 

Each Row has projectvalues for a whole month, e.g. 202406.

I Also have a Sprint Dimension, where each Month consists of two sprint, e.g. Sprint 1 202406, Sprint 2 202406.

I need to connect the tables without duplicating Values in the fact table 

[Anonymous · Accepted solution]

Hi @BIlix 

What is your expected outcome? There are several ways to handle many-to-many relationships, but the best practice may vary depending on your needs. 

If you are happy with removing duplicates, you can use Power Query to remove rows with duplicated values. You can also aggregate values. These can help remove duplicated values from the model to avoid many-to-many relationship. 

If you don't want to remove any data, you can extract yearmonth values into a new column in the Sprint Dimension table. Add a Date or Calendar table which has yearmonth column as a bridge table. Then create relationships between the Date table and the other two tables on yearmonth. Although the relationship may be many-to-many, but you can modify the cross-filter direction from both to single.

You can also create a many-to-many relationship between the Project table and the Spring Dimension table and keep the cross-filter direction as both or single if you are sure about it. Then you can use DAX methods like measures to get the value you want. 

Hope my suggestions can provide some directions to explore. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[lbendlin]

please show your data model.