Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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]))))
how to showed it correctly?
thanks
--Tonny
Solved! Go to 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
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] )
)
)
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
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