Need help creating a data model!

[cdawidow]

Hi guys, I am still new to power bi but I am having trouble modelling my relationships.  I have one column value (contract name) which is distinct and unique but everytime I set up a model I can only set it up as mant to many which in turn results in total sales calculations by contract name to be wrong . I am wondering if anybody had any advice on how I could set up my model appropriatly.  

This excel chart is just a snippet of the info I am trying to connect.  Any help would be appreciated !

[v-alq-msft]

Hi, @cdawidow 

Generally, we don't recommend relating two fact-type tables directly using many-to-many cardinality. Instead of relating fact-type tables directly, we recommend you adopt Star Schema design principles. You do it by adding dimension-type tables. The dimension-type tables then relate to the fact-type tables by using one-to-many relationships. This design approach is robust as it delivers flexible reporting options. It lets you filter or group using any of the dimension-type columns, and summarize any related fact-type table. You may create a model like below.

About many-to-many relationship guidance, I'd like to suggest you refer to the document  .

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

When you look at your Contract column in Data View do it show that the # of distinct values is same as # of rows?  If not, you can do the following in the query editor on that column:

1. Remove blank rows

2. Remove duplicates

3. Also make sure there are not two values that are the same but differ in upper vs lower case (M is case sensitive but DAX/relationships are not.  If so, you can apply UPPERCASE or lowercase to that column before your Remove Duplicates step to address that.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat