Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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!

1 Reply