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])
)
Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
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])
)