Hello everyone.
I have a problem with a many-to-many relationship.
My fact table has a structure like this:
| CompanyID |
OfficeID |
DepartmentID |
Amount |
| 1 |
10 |
200 |
55 |
| 1 |
20 |
100 |
700 |
| 1 |
10 |
200 |
300 |
| 2 |
50 |
700 |
150 |
Now I would like to be able to create a dimension to filter out different areas. However, these dimensions have a sort of hierarchical structure. For example, choosing area A would filter out all transactions with CompanyID=1 and OfficeID=10 regardless of DepartmentID. But if I choose are B I want the same result but only DepartmentID 100.
How do I achieve this? I can't create composite keys as the dimension values overlap and one transaction may belong multiple areas.
Any help would be much appriciated!
