Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
luandrad
Helper I
Helper I

Employee Headcount history changes

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:

Employee Tables.PNG

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

 

Wkr_Hist_month.PNG

 

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

 

Headcount.PNG

 

Headcount_2.PNG

 

The rules is:

 

  1. 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";
  2. 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).
  3. 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

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

11 REPLIES 11
luandrad
Helper I
Helper I

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

Anonymous
Not applicable

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

D

 

Anonymous
Not applicable

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.
Anonymous
Not applicable

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.

Anonymous
Not applicable

@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
Anonymous
Not applicable

@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.

Anonymous
Not applicable

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

D

Anonymous
Not applicable

@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.

Anonymous
Not applicable

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

D

Anonymous
Not applicable

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):

Skärmklipp2.PNG

Anonymous
Not applicable

Because it's always best to use variables.

Best
D

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors