Forum Discussion
Filtering across tables
Hi. I am currently working on a report and i am havign some issues. I have two tables - one is called "Merged" which is a headcount dataset, and a termination table. I have then created a "Hierar...
freginier
Solution Sage
Solution SageHey there!
You should check the relationship types and Cardinality
Ensure that SeniorityCategoryTable has a one-to-many relationship with both Merged (headcount dataset) and Termination (leavers dataset).The relationships should be:
- SeniorityCategoryTable [SeniorityCategory] → Merged [SeniorityCategory] (one-to-many)
- SeniorityCategoryTable [SeniorityCategory] → Termination [SeniorityCategory] (one-to-many)
If filters are not propagating correctly, create a DAX measure to ensure data is correctly aggregated:
Headcount_by_Seniority =
CALCULATE(
COUNT(Merged[EmployeeID]),
TREATAS( VALUES(SeniorityCategoryTable[SeniorityCategory]), Merged[SeniorityCategory] )
)
Leavers_by_Seniority =
CALCULATE(
COUNT(Termination[EmployeeID]),
TREATAS( VALUES(SeniorityCategoryTable[SeniorityCategory]), Termination[SeniorityCategory] )
)
Use these measures in your visualizations instead of relying on direct relationships.
Hope this helps!
😁😀