Forum Discussion

Decal's avatar
Decal
Helper I
2 years ago
Solved

Bad Hire Measure Filter

I'm trying to create a Bad Hire Measure filter resulting in a 1 or a 0 so that I can use this to filter my normal terminatinos number.  This is for an HR Dashboard.  A "Bad Hire" is considered a terminated employee where they were here longer than 1 day but less than a year.  

I've been able to build a measure for Bad Hire and use this, but I'd like to be able to just use my normal terminations measure and overlay this as a filter on the visual (or the page).

 

A "1" should be bad hire.  A "0" should not be a bad hire.

 

This is what I've built so far, which is wrong:

Bad Hires Filter =
VAR _tenure = CALCULATE(
    AVERAGEX(dimWorker,DATEDIFF(dimWorker[Last Hire Date],dimWorker[Term Date],DAY)),
    dimWorker[Terminated?]="Yes"
)

RETURN
IF(AND(_tenure<365.25,_tenure>1),1,0)
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    My recommendation is to use Power Query to precalculate the 'Days Employed' as its own field. Then your filter is very easy based around NOT ISBLANK(dimWorker[Term Date]) and AND(dimWorker[DaysEmployed] > 1, dimWorker[DaysEmployed] <= 365
    You'll find this will perform much better.

     

    In Power Query you can use Duration.Days([End Date] - [Start Date]).  All you'd need to do is use an IF statement to avoid scenarios where EndDate is blank (make it todays date instead)

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    My recommendation is to use Power Query to precalculate the 'Days Employed' as its own field. Then your filter is very easy based around NOT ISBLANK(dimWorker[Term Date]) and AND(dimWorker[DaysEmployed] > 1, dimWorker[DaysEmployed] <= 365
    You'll find this will perform much better.

     

    In Power Query you can use Duration.Days([End Date] - [Start Date]).  All you'd need to do is use an IF statement to avoid scenarios where EndDate is blank (make it todays date instead)