Latest Enterprise DNA Initiatives

Avoiding a Many To Many Relationship

Power BI
#1

I have 10 separate databases- one for each Company. Each Company has branches and departments.

Each Company(DB) has a BranchTable, a Department table and a Transaction Table. In the attached pbix file I have a simple example for Branches derived from tables in Excel.

I have established a relationship between the two tables for Branch Code - however, it is a many to many relationship. While a newbie to PowerBI, I remember Sam saying that many-to-many relationships are / should be the exception. I will also have the same issue when I bring in departments within a branch (within a company)

In this example do I have to settle for the many-to-many or is there a way I can establish one-many relationships?

if I do have to settle for the many-to-many what do I need to watch out for?

Thank You

Allister

ManytoMany.pbix (46.1 KB)

#2

@AllisterB,

The way I would do this is to set up a composite key in each table that is the concatenation of the branch code and company code, and then you can establish a one to many relationship from the dimension table to the transaction table as such:

image
image901×680 18.5 KB

I would do the concatenation in Power Query, but since I didn’t have the Excel file available I did it here as a calculated column:

BrCompKeyTran =
CONCATENATE( ‘tran’[BranchCode],‘tran’[CompanyCode])

I hope this is helpful.

  • Brian
3 Likes
#3

@AllisterB The other method would be to create a bridging table either in power query or with DAX. In Power Query first you have to duplicate the queries, only retain the branch code in each duplicated queries, merge queries as new and then remove the duplicates from the merged query. Finally, uncheck the Enable Load option from the duplicated queries.

1
1936×600 13.1 KB
2
3
31241×341 30.3 KB

With DAX, you can create a new table to achieve the same result:

4

ManytoMany.pbix (38.4 KB)

Thanks.

2 Likes
#4

@BrianJ and @MudassirAli are right on the money with their recommendations (personally I use Brian’s method, but that’s just what works best in my model).

As to WHY you want to avoid many-to-many relationships, take a look at the difference in the numbers on these two visuals. The top uses your Many-to-Many relationship, the bottom uses Brian’s One-to-Many relationship. (I had to add transaction amounts to the model to point this part out)

image
image443×603 16.6 KB
Many-To-Many Pitfalls.pbix (48.7 KB)

3 Likes
#5

Thank you everyone - I think that will work.

@Brian can I use PQ Editor in PowerBI to do what you suggest.
@mudassirali If I use the DAX solution, how would it handle duplicates in the Bridge Table - or is this not possible?

I have looked for material about Bridging Tables on DNA … but can’t find any - any suggestions where I might find the material?
Thanks

Allister

#6

@AllisterB You can certainly apply the same method as suggested by @BrianJ in power query which is the preferable method.
In Dax, the function is wrapped around DISTINCT function that will take care of the duplicates.
For resource on bridging table, check the following videos:

Thanks.

#7

@AllisterB,

Very straightforward to do this in Power Query directly through the UI. Here’s a quick gif I put together demonstrating how to do it: (hit F5 to re-run)

ABB80238-88F0-48D0-A7DE-07B33E77D7D5
ABB80238-88F0-48D0-A7DE-07B33E77D7D5800×282 498 KB

I hope this is helpful.

– Brian

#8

@MudassirAli

Thank You
I was thinking that the PQ approach requires an additional column of data whereas the DAX approach doesn’t - is the PQ superior to the DAX and therefore warrants the additional column.

Thank you too for the helpful youtube videos.

Allister

#9

@AllisterB
In power query additional column was created while with DAX I created a table (bridging table). Whatever transformation you can do in power query is always better than DAX. You can try both the methods and see what works better for you now and what will be better in future also.

Thanks.

#10

Hi @AllisterB, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!