Duplicates on table due to relationship model

[o59393]

Hi all

I created a many to many relationship and in both directions. It's showing me duplicate vales for an item with different locations:

As seen on the left table, the first 3 rows have the same values eventhough the right table shows the real value per each.

Is there any way to correct the left table duplicate values by fixing the relationship model, without having to create a concatenation of the Site, item and location in the master table of the pbix attached?

 A concatanation of the 3 columns in each of the tables would add memory to my pbix, so wanted to know if there is an easier way to solve this.

Pbix: https://1drv.ms/u/s!ApgeWwGTKtFdhnU5C-gKj_H2iktt?e=HTsnWj

Thanks!

[AllisonKennedy]

I would recommend creating a unique ID for this that can relate the data, even if that's not a pure concat of the three columns. Many to Many has special use cases, and is more suited for when you expect the same value for multiple items, as the article below tries to explain.
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-many-to-many-relationships

Can you explain what the Master Table purpose is and why you have it? Seems like you could just go directly from Query1 to Location to further reduce the size of your model?

You could get the answer you want using DAX, but it will be slow and not ideal for perforamnce, I would recommend getting the data model built properly instead.

[o59393]

Hi @AllisonKennedy 

My bad, I forgot adding the month and year to the Query1 table. By doing so, we will have duplicates of Site&Location&Item

So the master table, basically contains the unique values for site, location & item, in order to have this:

Where column 1 in each table is: Site & Location & Item.

If you thinks it's properly built let me know 🙂

https://1drv.ms/u/s!ApgeWwGTKtFdhnU5C-gKj_H2iktt?e=xrUJtj

Thanks!

[mahoneypat · Accepted solution]

You really should fix your model.  Many:Many relationships with bi-directional filters is a bad way to start learning Power BI.  Making a Master Table is a good idea, and you should transform that to make it so it can be on the 1 side of relationships to the other two tables.  The concatentation is a good way to do that, and I expect the model simplicity would far outweigh the file size impact.

Ok.  Off my soapbox, here is an expression that gets your desired results.

New Measure =
CALCULATE (
    SUM ( Query1[Units] ),
    TREATAS ( VALUES ( 'Master Table'[Location] ), Query1[Location] ),
    TREATAS ( VALUES ( 'Master Table'[Site] ), Query1[Site] )
)

Note that this a work around for not having a proper relationship (back on my soapbox I guess).  The two TREATAS clauses pass the filters that a concatenated relationship would have done automatically (i.e., your measure would just be SUM(Query1[Units]).

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

Regards,

Pat

[o59393]

Hi @mahoneypat 

Agree, many to many could cause many problems.

I fixed my model and did it one to many:

Being Column 1= a concatenation of Location & Site & item

I checked your measure and matched with the units fixing the model:

I take your point on simplicity outweighes the size of the file.

Here is my pbix with the model fixed and your measure.

https://1drv.ms/u/s!ApgeWwGTKtFdhnU5C-gKj_H2iktt?e=xrUJtj

Thanks

[mahoneypat]

Glad you got it working and that you improved your model.  I always say when I teach Power BI - "Simple model, simple DAX".

Regards,

Pat