Equivalent of LEFT JOIN subquery containing distinct in DAX

Viewed 201

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.

1 Answers

Try to SUMX with the DISTINCT to get the unique values of the department :

Employee Count By Dept  = 
SUMX (
DISTINCT(VALUES(dim_department[department])),
CALCULATE (
DISTINCTCOUNT(fact_table[employeeID]),
DIM_Status[employee_status] <> 2
)
)
)
Related