Equivalent of LEFT JOIN subquery containing distinct in DAX

[AmiraBedh]

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.

[amirabedhiafi · Accepted solution]

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
)
)
)

[parry2k]

@AmiraBedh it will be easier if you provide sample data and the expected output. 

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[AmiraBedh]

I updated the question.