Forum Discussion
Relationship between two tables based off multiple columns?
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!
10 Replies
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
