Distinct count of active employees over time period

[key_to]

Hello.

I am strugglig to get my number of employees by month right. The issue is that some employees hold two positions and appear each month twice. I don't know how to include "if a variable is true, count distinct"  into my formula below.  The 'Var result' with the 'if' formula does not work. 

Distinct count of active employees =

VAR currentDate =

    MAX ( 'DimDates'[Dates] )

VAR _active =

        FILTER (

            FactEmployement,

            ('FactEmployement'[Ansatt fra] <= currentDate

                && OR((FactEmployement[Ansatt til] >= currentDate ), ISBLANK(FactEmployement[Ansatt til]))

        )

    )

    VAR result = CALCULATE(DISTINCTCOUNT(FactEmployement[Ansattnr]),IF(_active=1,1,0))

    Return result

I would appreciate any suggestion!

Thanks in advance!

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but please try disconnect the relationship between date table and fact table. And then try the below measure.

Distinct count of active employees =
VAR _active =
    SUMMARIZE (
        FILTER (
            FactEmployement,
            (
                'FactEmployement'[Ansatt fra] <= MAX ( 'DimDates'[Dates] )
                    && OR (
                        ( FactEmployement[Ansatt til] >= MIN ( 'DimDates'[Dates] ) ),
                        ISBLANK ( FactEmployement[Ansatt til] )
                    )
            )
        ),
        FactEmployement[Ansattnr]
    )
VAR result =
    COUNTROWS ( _active )
RETURN
    result

[key_to]

Thank you so much! This is exactly what I was looking for!