Forum Discussion

naninamu's avatar
naninamu
Helper IV
1 year ago
Solved

Many to Many help

Hi - I have an issue where I have a large report with the following Many to Many relationship. As far as I can tell it's not actually causing any issues, but I know it's bad practice, and I think per...
Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    Hi naninamu ,

    Use star schema always you can, so i recommend you to create a bridge table to avoid many-to-many relationships:

    Create a Bridge Table

    GroupBridge = DISTINCT('Group Table'[GROUP])

    Connect Item Table[GROUP] to GroupBridge[GROUP] (one-to-many).
    Connect Group Table[GROUP] to GroupBridge[GROUP] (one-to-many).

     

    Find ITEMS containing a specific THING by this DAX:

    ItemsContainingThing = 
CALCULATE(
    DISTINCT('Item Table'[ITEM]),
    TREATAS(VALUES('Group Table'[THING]), 'Group Table'[THING])
)

     

    Find THINGS in a specific ITEM:

    ThingsInItem = 
CALCULATE(
    DISTINCT('Group Table'[THING]),
    TREATAS(VALUES('Item Table'[ITEM]), 'Group Table'[GROUP])
)
naninamu's avatar
naninamu
Helper IV
1 year ago

Thanks for your help! I had tried something like that previously, but it didn't work.

 

I'm still getting an error using your method saying there is no relationship between the 2 tables I connected using the bridging tables, but suspect it is related to something else in the tables (as I said this is a simplified example).

Currently investigating - thanks for putting me on the right path!