Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Filtering duplicate values in measure

Hi all,

 

I have a table like this:

 

Employee #Job Information: DateLocationDivisionDepartmentJob TitleJob Information end date
101/01/2018USAUSSalesSales Expert01/01/2019
101/01/2018USAUSSalesSenior Sales Expert 
201/01/2018GermanyEuropeSalesSales Expert01/01/2019
201/01/2018GermanyEuropeSalesSenior Sales Expert05/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
Need Help
Show and Tell

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    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.