topic Re: Slow Dax Measure for Employee Turnover in DAX Commands and Tips

Slow Dax Measure for Employee Turnover

dcrow5378 — Fri, 04 Mar 2022 16:30:59 GMT

Hello,
I have a few dax measures that are calculating employee turnover rates. The problem is that it takes over a minute for the visuals/tables to render if the turnover measure is used. I wonder if anyone can take a look and see any glaring issues. Thanks in advance for any assistance. The data set is only around 9k rows.

Turnover =
IFERROR ( [Termed] / [AVG Emps], BLANK () )

----------------------------------------------------------------------

Termed =
CALCULATE (
    COUNT ( Turnover[Employee ID] ),
    FILTER (
        ALL ( Turnover[TermDate] ),
        Turnover[TermDate] >= MIN ( 'Date'[Date] )
            && Turnover[TermDate] <= MAX ( 'Date'[Date] )
    )
)

----------------------------------------------------------------------

AVG Emps =
AVERAGEX ( ALLSELECTED ( 'Date' ), [Employee Count] )

----------------------------------------------------------------------

Employee Count =
SUMX (
    GENERATE (
        CALCULATETABLE (
            SUMMARIZE (
                Turnover,
                Turnover[Hire Date],
                Turnover[TermDate],
                "Rows", COUNTROWS ( Turnover )
            ),
            ALL ( 'Date' )
        ),
        INTERSECT (
            DATESBETWEEN ( 'Date'[Date], Turnover[Hire Date], Turnover[TermDate] ),
            LASTDATE ( 'Date'[Date] )
        )
    ),
    [Rows]
)

Re: Slow Dax Measure for Employee Turnover

amitchandak — Fri, 04 Mar 2022 17:01:35 GMT

dcrow5378 , Can you explain these formulas. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

refer if the approach on this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Re: Slow Dax Measure for Employee Turnover

littlemojopuppy — Fri, 04 Mar 2022 17:20:36 GMT

Hi dcrow5378

Here are some measures I wrote for my job...

Headcount = VAR EveryoneHired = FILTER ( Employees, Employees[DateStarted] <= MAX ( 'Calendar'[FullDate] ) ) VAR TerminatedEmployees = FILTER ( Employees, Employees[Active] = FALSE () && Employees[DateTerminated] <= MAX ( 'Calendar'[FullDate] ) ) VAR ActiveEmployees = EXCEPT ( EveryoneHired, TerminatedEmployees ) RETURN COUNTROWS ( ActiveEmployees ) Hires = VAR NewHires = CALCULATE ( DISTINCTCOUNT ( Employees[EmployeeID] ), USERELATIONSHIP ( 'Calendar'[FullDate], Employees[DateHired] ) ) VAR Rehires = CALCULATE ( DISTINCTCOUNT ( Employees[EmployeeID] ), NOT ( ISBLANK ( Employees[DateRehired] ) ), USERELATIONSHIP ( 'Calendar'[FullDate], Employees[DateRehired] ) ) RETURN NewHires + Rehires Terminations = CALCULATE ( DISTINCTCOUNT ( Employees[EmployeeID] ), Employees[Active] = FALSE (), USERELATIONSHIP ( 'Calendar'[FullDate], Employees[DateTerminated] ) ) Median Tenure = VAR EveryoneHired = FILTER ( Employees, Employees[DateStarted] <= MAX ( 'Calendar'[FullDate] ) ) VAR TerminatedEmployees = FILTER ( Employees, Employees[Active] = FALSE () && Employees[DateTerminated] <= MAX ( 'Calendar'[FullDate] ) ) VAR ActiveEmployees = EXCEPT ( EveryoneHired, TerminatedEmployees ) RETURN MEDIANX ( ActiveEmployees, YEARFRAC ( Employees[DateStarted], TODAY () ) ) Turnover Rate = VAR PastYear = CALCULATETABLE ( VALUES ( 'Calendar'[CalendarWeekEnd] ), DATESINPERIOD ( 'Calendar'[FullDate], MAX ( 'Calendar'[FullDate] ), -1, YEAR ), REMOVEFILTERS ( 'Calendar'[FullDate] ) ) VAR WeeklyAmounts = CALCULATETABLE ( ADDCOLUMNS ( PastYear, "WeeklyHeadcount", [Headcount], "WeeklyTerminations", [Terminations] ), REMOVEFILTERS ( 'Calendar'[FullDate] ) ) VAR AverageWeeklyHeadcount = AVERAGEX ( WeeklyAmounts, [WeeklyHeadcount] ) VAR TotalTerminations = SUMX ( WeeklyAmounts, [WeeklyTerminations] ) RETURN DIVIDE ( TotalTerminations, AverageWeeklyHeadcount, BLANK () )

Part of why your measures are so slow is using the SUMMARIZE function. Try to avoid using that function...

These measures are for the data model I created at work. But you should be able to follow the logic and implement in your model. Hope this helps!

Re: Slow Dax Measure for Employee Turnover

Kucrapok — Fri, 04 Mar 2022 17:26:48 GMT

Hi dcrow5378,

1) Termed measure
Is there a specific reason why you need to explicitly filter the dates with MIN and MAX? Don't you have a relationship between Turnover[TermDate] an Date[Date]?

2) Employee Count
What are you trying to accomplish here?
Like amitchandak said, show us the Data and the model and explain to us with this measure should represent and what is the expected result. There's probably a better way to code this.

Also, its really not best pracetice to use SUMMARIZE to do calculation. You should replace the syntax to :

ADDCOLUMNS( SUMMARIZE( Turnover, Turnover[Hire Date], Turnover[TermDate] ), "Rows", CALCULATE( COUNTROWS ( Turnover )) )

Most likely, the problem is

Re: Slow Dax Measure for Employee Turnover

AlexisOlson — Fri, 04 Mar 2022 21:28:40 GMT

It's not surprising that GENERATE inside of SUMX inside of AVERAGEX is slow.

Rewriting [Employee Count] is what I'd recommend. You can probably make it much simpler. Maybe something more like [Termed]?

Employee Count = VAR _MinDate = MIN ( 'Date'[Date] ) VAR _MaxDate = MAX ( 'Date'[Date] ) RETURN CALCULATE ( COUNT ( Turnover[Employee ID] ), FILTER ( ALL ( Turnover[TermDate] ), ISBLANK ( Turnover[TermDate] ) || Turnover[TermDate] >= _MinDate ), Turnover[HireDate] <= _MaxDate )

Re: Slow Dax Measure for Employee Turnover

dcrow5378 — Thu, 10 Mar 2022 19:57:06 GMT

Thanks! I'll give these a shot and let you know how it goes.

Re: Slow Dax Measure for Employee Turnover

dcrow5378 — Thu, 10 Mar 2022 19:58:04 GMT

I tried this and the numbers are coming out much too low.