I have the following query :
SELECT d.department
,COUNT(*)
FROM fact_table AS f
LEFT JOIN
(SELECT DISTINCT sk_dept, department FROM dim_department ) AS d ON d.sk_dept=f.fk_dept
WHERE f.employee_status <> 2
GROUP BY d.department
This is the output of the query :
Department Count
1 250
2 300
The department dimension is like below :
DepartmentSK DepartmentId SubDepartment
1 1 23
2 1 67
3 1 120
4 2 44
The Dept has sub department my measure is showing me duplicates :
Employee Count By Dept =
CALCULATE (
DISTINCTCOUNT( fact_table[employeeID]),
DIM_Status[employee_status] <> 2
)
How can we include the LEFT JOIN subquery containing distinct in the DAX measure?
The output I get with the DAX formula :
Department Count
1 500
2 600
The count is duplicated because the deptid is existing many times since each department has many sub departments.