Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
It sounds like a scenario for The As-Of Table - Qlik Community - 1466130.
- Marcus