Filtering duplicate values in measure

[Anonymous]

Hi all,

I have a table like this:

Employee #	Job Information: Date	Location	Division	Department	Job Title	Job Information end date
1	01/01/2018	USA	US	Sales	Sales Expert	01/01/2019
1	01/01/2018	USA	US	Sales	Senior Sales Expert
2	01/01/2018	Germany	Europe	Sales	Sales Expert	01/01/2019
2	01/01/2018	Germany	Europe	Sales	Senior Sales Expert	05/01/2019

Job information: date; blank means the person is still active in this role.

I am creating a visual to see the number of people per location in years, and to calculate the numbers, I use the following measure:

% isDateActiveEmployeeDistinctCount = CALCULATE(

DISTINCTCOUNT('_FactJobInformationUnion'[Employee #]),

FILTER(

_FactJobInformationUnion,

AND(

AND(

_FactJobInformationUnion[Job Information: Date] <> BLANK(),

_FactJobInformationUnion[Job Information: Date] <= MAX(_DimDate[Date])

),

OR(

_FactJobInformationUnion[Job Information: EndDate] = BLANK(),

_FactJobInformationUnion[Job Information: EndDate] >= MIN(_DimDate[Date])

)

)

),

FILTER(

__EmployementDates,

OR(

__EmployementDates[Termination Date] > MIN(_DimDate[Date]),

__EmployementDates[Termination Date] = BLANK()

)

)

)

However, as you can see, if one person changed roles within a year (let's say in 2019), my formula calculates this person as two, taking both roles into consideration. Therefore, I am trying to find a way to insert one step in the formula, which will give me a distinct result per person, showing only the latest role (if this person is still active, the formula needs to pick the data with blank job information: date, if it's not active, then it should pick the most recent date in the job information: date field).

I hope I am able to explain the situation and the issue.

Appreciate your support on this.

Best regards,

Ugur

[v-yuta-msft]

@Anonymous ,

Not very clear about your requirement, maybe you can modify your measure like below:

% isDateActiveEmployeeDistinctCount =
CALCULATE (
    DISTINCTCOUNT ( _FactJobInformationUnion[Employee #] ),
    FILTER (
        ALLEXCEPT ( _FactJobInformationUnion, _FactJobInformationUnion[Employee #] ),
        AND (
            AND (
                _FactJobInformationUnion[Job Information: Date] <> BLANK (),
                _FactJobInformationUnion[Job Information: Date] <= MAX ( _DimDate[Date] )
            ),
            OR (
                _FactJobInformationUnion[Job Information: EndDate] = BLANK (),
                _FactJobInformationUnion[Job Information: EndDate] >= MIN ( _DimDate[Date] )
            )
        )
    ),
    FILTER (
        __EmployementDates,
        OR (
            __EmployementDates[Termination Date] > MIN ( _DimDate[Date] ),
            __EmployementDates[Termination Date] = BLANK ()
        )
    )
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.