I am building a Diversity & Headcount dashboard and discovered 48 employees have multiple ethnicity records (47 with 2 records, 1 with 4 records), causing a discrepancy between my headcount totals depending on which dimensions are included.
Issue: Headcount is only inflated when Gender and Ethnicity are used together. Ethnicity alone returns the correct count despite 48 employees having multiple ethnicity records.
- Table 1 – Headcount only ✅
- Table 2 – Gender + Headcount ✅
- Table 3 – Ethnicity + Headcount ✅
- Table 4 – Gender + Ethnicity + Headcount …. Inflated by ~50
When auditing Table 4 with Employee ID, the 48 duplicate ethnicity records account for the inflation. What's confusing is that Table 3 does not inflate despite those same duplicates existing which is suggesting the issue is specific to how the Gender and Ethnicity dimensions interact at the join level rather than the duplicates alone.
Could this be a fan trap in the semantic model? And what is the recommended way to resolve headcount inflation caused by dimension join behavior in FDI?
Any insight appreciated!
