Forum Discussion
Measure based on measure doesn't work as expected
I have prepared a measure, which counts the cumulative number of employees for a given date. It takes into consideration "Date of employment" and "Date of dismissal". The table contains each employee no. together with the above dates.
The measure works as expected.
Active employees =
CALCULATE (
COUNTROWS (
FILTER (
'Employees',
'Employees'[Date of employment] <= MAX ( 'Calendar'[Date] )
&& (
ISBLANK ( 'Employees'[Date of dismissal] )
|| 'Employees'[Date of dismissal] > MAX ( 'Calendar'[Date] )
)
)
),
FILTER (
ALLSELECTED ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
Then I wanted to create a measure calculating no. of active female employees, so I used the above measure in my new measure:
No. of female employees =
CALCULATE (
[Active Employees],
FILTER ( 'Employees', Employees[Gender] = "Female" )
)
It doesn't work though as expected and gives only the number of female in selected date/period (not cumulative).
Does anybody know why?
When I adjust the first measure with the last filter it works fine.
Active Female employees =
CALCULATE (
COUNTROWS (
FILTER (
'Employees',
'Employees'[Date of employment] <= MAX ( 'Calendar'[Date] )
&& (
ISBLANK ( 'Employees'[Date of dismissal] )
|| 'Employees'[Date of dismissal] > MAX ( 'Calendar'[Date] )
)
)
),
FILTER (
ALLSELECTED ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
),
Employees[Gender] = "Female"
)
Thanks!
Refer if this can help
HR-Analytics-Active-Employee-Hire-and-Termination-trend
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
1 Reply
- amitchandakSuper User
Refer if this can help
HR-Analytics-Active-Employee-Hire-and-Termination-trend
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin