Hi,
I’m trying to use DAX to create a headcount measure based on the employee hire date and termination date. Here is what I have come up with so far:
Running Head Count =
CALCULATE (
DISTINCTCOUNT ( ‘Employee Demographics’[employee_number] ),
FILTER (
ALL ( ‘Employee Demographics’ ),
OR (
AND (
MAX ( ‘Date Time Table’[StandardDate] ) >= ‘Employee Demographics’[hire_date],
MAX ( ‘Date Time Table’[StandardDate] ) < ‘Employee Demographics’[termination_date]
),
OR (
AND (
MAX ( ‘Date Time Table’[StandardDate] ) >= ‘Employee Demographics’[hire_date],
‘Employee Demographics’[termination_date] = BLANK ()
),
OR (
AND (
MAX ( ‘Date Time Table’[StandardDate] ) >= ‘Employee Demographics’[hire_date],
‘Employee Demographics’[hire_date] > ‘Employee Demographics’[termination_date]
),
AND (
MAX (‘Date Time Table’[StandardDate] ) = ‘Employee Demographics’[hire_date],
MAX (‘Date Time Table’[StandardDate] ) = ‘Employee Demographics’[termination_date]
)
)
)
)
)
)
This works great except for one small problem. I have some bad data that I cannot get cleaned up at the source at this point in time, and as a result there are 24 employee records that are included no matter what date is used. All of these records have a worker status column value that is blank, and no other employee records in my dataset have a blank worker status column value (they are either Active, On Leave, or Terminated). Granted, I could manually filter these out in Power Query, but I’d rather learn how to use DAX to exclude these rows - I would like my DAX calculation to filter out these 24 rows that have this blank worker status value.
I have tried to use the ALLEXCEPT function, but I don’t think it is meant to work the way I’m trying to use it – at least I can’t get it to work this way.
What I’ve come up with is to use the CALCULATETABLE function to essentially clone my ‘Employee Demographics’ table and in the process filter out the 24 offending rows. Then I updated my DAX above to point to the newly calculated table.
I have two questions for you:
-
Is there a way to combine the CALCULATETABLE and CALCULATE function within DAX measure statement? If so, can you provide or point me to an example of how that would need to be written?
-
If you can’t combine these two functions within one DAX measure statement, is the mechanism I’ve come up with the best way to accomplish my goal of filtering out those 24 records from my employee count measure? If you have suggestions for a better / more efficient / less taxing / more elegant method, I’d love to hear what your think.
Thanks in advance for your feedback!
Trey