Many to Many help

[naninamu]

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 performace will suffer as the model grows.

To give a simplified example

• I have a bunch of Items
• Each item belongs to a Group
• A Group contains different Things  ie - Group A contains, Spoons, Forks and Knives while Group B contains Spoons, Towel and Jars

ITEM TABLE		GROUP TABLE
ITEM	GROUP		GROUP	THING
A	aaa		aaa	Spoon
B	aaa		aaa	Fork
C	bbb		aaa	Knife
D	bbb		bbb	Spoon
E	ccc		bbb	Towel
F	ccc		bbb	Jar
			ccc	Fork
			ccc	Iron
			ccc	Knife

So when linked on GROUP, it is a Many to Many relationship.

I tried putting in a one column Bridging table containing just the unique GROUPS, but it broke my model. Also, I have it as bi-directional as people need to be able to filter by the THING (ie which ITEMS contain forks?) but also filter by the THING (what's in ITEM A?)

This is a simplified example but it is the problem I have - a Many to Many bidirectional relationship that works, but that I'm nervous about.

Any help much appreciated!!!

[naninamu]

Hi @Bibiano_Geraldo - here is a file. On the first tab is an example of the table I'm trying to buiild that isn't working. On the second tab is a screenshot of what I see using Many to Many which is what I want to see using a bridge. Many thanks in advance. 

Bridge issue.pbix

[naninamu]

Hello @Bibiano_Geraldo  - thanks for helping me out. I'm still getting an error, so thought I'd post my actual model to see if perhaps you could identify where I'm going wrong.

So in the above, each FileName (Item in my prev example) can be associated to different HashedSensitiveInfoTypes (Groups). Each HashedSensitiveInfoType has multiple SIT Names (Things).

Ultimately I'm trying to create a table listing the Filenames and all the SITs that belong to it.

I thought the issue might be with the Filenames as one file can have more than one HashedSensitiveInfoType - so I created an index and concatenated this with the Filename to give a unique value for each.

I could then drag in the HashedSensitiveInfoType value into my table that corresponded, but when I then tried to drag in SITNames to understand which SITNames fell under the  HashedSensitiveInfoType, it gave me an error.

I can also have HashedSensitiveInfoType and SITName in a column without Filename and that works, but not all three.

Note when I use a many to many this works fine, but I know it's not best practice.

Any help much appreciated! Cheers, Andrew

[Bibiano_Geraldo]

Hi @naninamu,

Can you please share a no sensitive information file pbix to see closer the problem? You have to upload to the cloud like onedrive or other and share the link for download here in comments.

[naninamu]

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!

[Bibiano_Geraldo · Accepted solution]

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])
)