Filtering across tables

[faaz]

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 "Hierarchy" table to connect the two tables. The issue is however when i try to split headcount and terminations (leavers) out on seniority categories. It will only work on one of them. I have tried creating a column in each dataset called "SeniorityCategory" but that did not work. I also tried making a "SeniorityCategoryTable" to connect the two tables, however, this did not work either. Does anyone know, how I can fix this? 

[v-kathullac]

Hi @faaz ,

we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @faaz ,

we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @faaz ,

we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[freginier]

Hey 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!

😁😀