Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |