calculate active employee number in each department

[tonny_bwg]

Hi, Gurus, need you help to solve this problem. 

Create 1 to many relationships on department field

DAX to calculate active employee numbers

在职人数 = CALCULATE([基础人数],           //countrows(employee table)

   dim_Date[Date]<=MAX(dim_Date[Date]),

   filter(VALUES('Fct_Employees'[Hire Date]),Fct_Employees[Hire Date]<=MAX(dim_Date[Date])),

                     filter(VALUES('Fct_Employees'[Leave Date]),OR('Fct_Employees'[Leave Date]>Max(dim_Date[Date]),ISBLANK('Fct_Employees'[Leave Date]))))

1. want to show active employee in each department, just show the summarized number.

how to showed it correctly? 

thanks 

--Tonny

[Anonymous]

Why do you need the first filter? ( dim_Date[Date]<=MAX(dim_Date[Date]))

Ty as well to use just one filter function. Would you try this?

在职人数 =
CALCULATE (
    [基础人数],
    //countrows(employee table),
    FILTER (
        'Fct_Employees',
        Fct_Employees[Hire Date] <= MAX ( dim_Date[Date] )
            && Fct_Employees[Leave Date] > MAX ( dim_Date[Date] )
            && ISBLANK ( 'Fct_Employees'[Leave Date] )
    )
)

[tonny_bwg]

Hi, Jcalheir

I found the root cause of this issue, it was caused by a blank existed in front of field value. removed it, it shows correlcy.

thanks 

--Tonny

[tonny_bwg · Accepted solution]

hI, jcalheir

thank you so much for the help, try it, but still wrong.  however , when i put department field of employee table into matrix, it shows correct number for each department.

please see below figures