topic Re: Employee Headcount history changes in DAX Commands and Tips

Employee Headcount history changes

Anonymous — Thu, 20 Feb 2020 14:25:35 GMT

I need a measure to calculate the employees that were actives in some period and there respective "Cost Center" (department), this measure can't "summarize" the total when no filter data is applied in the slicers, should show the actual (last) value from the last month in the context.

I have one table of all Employess of the company, and in this table l have HireDate and FiredDate as well, and another table where I have the "history" changes of the employee, where I will have the start / end of the period that the employee was in determined Cost Center and his Rate, for example:

From this Hist Table, I created a new Table (DAX) to explode the data between Start / End date, as bellow:

With those data, I need one measure that will count if the employee is active in determined month/year and where we was allocated (Cost Center).

I have something like the DAX bellow, but the results are not what I expected:

Headcount = DISTINCTCOUNT(Worker_Hist[Employee ID])

Headcount_3 = CALCULATE( COUNTROWS( Worker_Hist ) ; FILTER ( VALUES( Worker_Hist[Start Date] ); Worker_Hist[Start Date] <= MAX ( Calendar[Date] ) ); FILTER ( VALUES( Worker_Hist[End Date] ); OR ( Worker_Hist[End Date] > MAX ( Calendar[Date] ); ISBLANK( Worker_Hist[End Date] ) ) ) )

Filter Year = 2019 / Month = All month

The rules is:

If the employee is fired, in his last month he can't be counted as active. (in 03/2019, the ID 12223 should be "0";
If the employee just change the CC or "Rate", he has to be count as Active until the month of the "End Date" (From hist).
The total has to be the actual active at the select year/month, or if no filter is applied, the Total should be the actual actives employee.

I hope that someone could help me with that.

Thanks in advanced.

Lúcio Andrade

Re: Employee Headcount history changes

Anonymous — Thu, 20 Feb 2020 21:20:20 GMT

I think the measure will work better if you check all conditions in one FILTER statement like this:

Headcount_4 = CALCULATE ( [HeadCount] FILTER ( Worker_Hist; Worker_Hist[Start Date] <= MAX ( Calendar[Date] ) && ( Worker_Hist[End Date] > MAX ( Calendar[Date] ) || ISBLANK ( Worker_Hist[End Date] ) ) ) )

Then of course you can add more logic to the filter as well.

Re: Employee Headcount history changes

Anonymous — Sun, 23 Feb 2020 19:28:59 GMT

Anonymous, on what grounds do you claim that a complex filter will be better than a set of simple filters? I'll surprise you - the opposite is actually true because of the way CALCULATE works under the hood and because of what it is optimized for.

Best
D

Re: Employee Headcount history changes

Anonymous — Sun, 23 Feb 2020 19:36:03 GMT

The description of the measure, especially the rules, is foggy. Too foggy to be able to build something useful. One thing I've noticed is that in your Headcount_3 measure you have COUNTROWS( Worker_Hist ) which calculates the number of rows in Worker_Hist. THis is almost surely wrong.

Re: Employee Headcount history changes

Anonymous — Sun, 23 Feb 2020 19:49:34 GMT

AnonymousAs always I think it depends. But I have worked on some quite large tables before and then the FILTER function can really kill the performance (since it iterates the whole table). But yes, in this case the oppisite might be true since the filter in on VALUES([column]) and not on the whole table. But it's hard to tell without seeing the complete dataset and test the different ways to write the DAX.

But the reason I suggested to have one more complex filter in this case was that there might be logic that has dependencies on row level that cant be separated into many simple filters. So it had nothing to do with performance.

I might have been a little bit to fast writing an answer in this case, the requirements are a little bit hard to follow... But I think it can at least give a hint. But more logic (that I didn't understand) perhaps needs to be added.

Re: Employee Headcount history changes

Anonymous — Sun, 23 Feb 2020 19:52:29 GMT

Try something like this:

Headcount = var __maxVisibleDate = MAX ( Calendar[Date] ) var __minVisibleDate = MIN ( Calendar[Date] ) return CALCULATE ( DISTINCTCOUNT ( Worker_Hist[Employee ID] ), KEEPFILTERS ( Worker_Hist[Start Date] <= __minVisibleDate ), KEEPFILTERS ( Worker_Hist[End Date] > __maxVisibleDate ) ) + CALCULATE( DISTINCTCOUNT ( Worker_Hist[Employee ID] ), KEEPFILTERS ( Worker_Hist[Start Date] <= __minVisibleDate ), KEEPFILTERS ( ISBLANK ( Worker_Hist[End Date] ) ) )

I can't do any better because I don't fully understand the definition of the measure.

Best

Re: Employee Headcount history changes

Anonymous — Sun, 23 Feb 2020 19:56:52 GMT

Well, Anonymous, sorry but you're not right. CALCULATE has been optimized to work with filters of the type:

table[column] = value

and nothing can be faster than this. There is some internal "magic" and tricks in the engine that make this true.

You can read about it on www.sqlbi.com or in The Book by The Italians (The Ultimate Guide to DAX).

Best

Re: Employee Headcount history changes

Anonymous — Sun, 23 Feb 2020 20:26:54 GMT

AnonymousYes, I'm aware of the CALCULATE magic on simple filters. But as far I have have understood it doesn't apply when you have filters with OR, MAX and other that was the case here. Then you cant use the simple CALCULATE([Measure], [Column]=xxx) format, you have to specify an implicit FILTER that is a lot slower. I think one of "the Italians" taught me this on my very first DAX course, but that was almost 10 years ago, so it might have been improved in newer versions? However, it is of cause better if you can have separate filters on columns instead of the whole table. But in some cases that could be hard because of the logic.

Re: Employee Headcount history changes

Anonymous — Sun, 23 Feb 2020 20:28:47 GMT

Just to drive the point home...

The formula in the first post contains, for instance, such a line:

FILTER ( VALUES( Worker_Hist[Start Date] ); Worker_Hist[Start Date] <= MAX ( Calendar[Date] ) )

This is nothing but

KEEPFILTERS( Worker_Hist[Start Date] <= value )

which does happen to be a simple filter for CALCULATE.

Apart from table[column] = value, the simple filters also contain the relational operators <, <=, >, >=.

Best

Re: Employee Headcount history changes

Anonymous — Sun, 23 Feb 2020 20:40:48 GMT

Ok , Anonymous thank's for information. Happy to learn something new 🙂 The reason I thought this was still not optimized is the error message you get when trying to write the measure in the "simple format" (or in KEEPFILTERS):

Re: Employee Headcount history changes

Anonymous — Sun, 23 Feb 2020 20:47:30 GMT

Because it's always best to use variables.

Best
D

Re: Employee Headcount history changes

Anonymous — Mon, 24 Feb 2020 12:57:19 GMT

Dear Anonymous and Anonymous thanks for your post and comments.

Actually the measures do not achived what I need. I'm preparing a PB sample to share here, just making sure that confidential data will not be shared.

Thanks.

Lúcio