Relationship between two tables based off multiple columns?

[aid928]

Hi all,

Hoping someone can help me with this one as it's really got me stuck.

Basically I have four tables. One table is a Cost Centre hierarchy, with high level codes feeding into lower level codes (L5 is a bottom level Cost Centre).

L1	L2	L3	L4	L5
1	12	123	1234	12345
1	12	123	1234	12333
1	12	123	1239	12392
1	15	154	1544	15448
2	22	224	2248	22482

Two other tables are Account Code and Activity coce hierarchies with a similar layout to the above.

I then have a Posting Rules table, where a Posting Rule can exist at any level in the hierarchy.

CC	ACC	ACT
1	x	yy
1	xx	yy
12345	x	yy
2248	xxx	yy
2248	xx	yy
22	xx	yy

Having real difficulty getting these tables linked. Even just focusing on the Cost Centre table, because a Posting Rule can sit at any level in a hierarchy, I'm not able to build a relationship between single columns.

We essentially want a user to be able to select a bottom level (L5) Cost Centre from a slicer (say 12345), and then see all Posting Rules directly against it, or that feed up through the hierarchy, so this would produce:

CC	ACC	ACT
1	x	yy
1	xx	yy
12345	x	yy

Any help to come to this solution would be greatly appreciated.

Thanks!

[aid928]

Hi tamerj1,

Thanks very much for producing this - I tried downloading the file but because we are on Power Bi Report Server and your file has many-to-many relationships, I'm unable to open it.

Are you able to explain how you linked the two tables together?

Thanks so much

[tamerj1]

@aid928 
From the model view just drag the CC from table to the other CC in the other table.

[aid928]

Hi @tamerj1 

Thanks again for explaining this, really useful for understanding.

Do you know if there would be any way to achieve this without a many to many relationship? Since we are on PBI Report Server I can't make a many to many relationship.

Thanks,

Aidan

[tamerj1]

@aid928 
Yes but would it be ok to have 1:* relationship but 2way?

[aid928]

Nope, says I can't make a relationship since the matching columns arent unique

[tamerj1]

@aid928 
I mean something like this

You vcan use power query or dax to create a unique table and use it as a bridge table. Would this make sense to you?

[aid928]

Ah apologies again, I had the relationship going to the wrong field. Have got it working, thank you so much!

[aid928]

Ah sorry have tried that now. Should my slicer still use the CC field from the Cost Centre table? When doing this and filtering against CC 12345, I am only getting Posting Rules directly against it rather than including CC 1 also.

[tamerj1]

@aid928 
The slicer is from 'Cost Center'[L5] column

[tamerj1 · Accepted solution]

Hi @aid928 
Please follow the steps and refer to attached sample file