Forum Discussion

o59393's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

Duplicates on table due to relationship model

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 ha...
Need Help
Show and Tell
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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's avatar
    o59393
    Icon for Post Prodigy rankPost Prodigy
    6 years ago

    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's avatar
      mahoneypat
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      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