Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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:

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:

 

  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

 

DAX
PowerBI Desktop
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago
      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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's avatar
    Anonymous
    Not applicable
    6 years ago
    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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
    Anonymous
    Not applicable
    6 years ago

    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