SUMX alternative for faster performance RRS feed

  • General discussion

  • I have a SUMX based measure that is doing what it needs to but is slow from a performance standpoint. 

    Available FTE:=SUMX(Employee,DIVIDE([Available Hours], [Timesheet Days] * Employee[Daily FTE Hours]))
    Timesheet Days:=CALCULATE(COUNT(WorkDateCalendar[Date]), WorkDateCalendar[Weekday Indicator]="Weekday", WorkDateCalendar[Week Type] <> "Future Weeks")
    Available Hours:=SUM(TimeData[Scheduled Hrs])

    We basically have a TimeData table that has 1 record per employee for every day an employee is active (this table will also have any time entry records based on time they have entered) with the number of scheduled hours for that employee. We also have an Employee table that basically has a list of all the employees with their key attributes. 2 key attributes are what Country they belong to and what is the standard Daily FTE hours for that country. 

    I basically want to represent a count of of all the Available Hours" for an employee in FTE terms.

    e.g. Let's say I have filtered the report for period between Jan and March (3 months).

    If an employee is in Canada and was active only for Jan and Canada let's say has daily work hours of 7 then and this employee also work 7 hrs a day then  in FTE terms he would show up as ~0.33 FTE. 

    If an employee is in US and was active fully from Jan to Mar and US let's say has daily work hours of 8 then but this employee is scheduled to work only 4 hrs a day then  in FTE terms he would show up as ~0.5 FTE. 

    So the measure above iterates through each employee and ultimately gets these answers. 

    My question is, is there another way to do this so the calculation can be faster than it is now?

    FYI... TimeData has about 40M records and Employee has about 100K records. 

    Tuesday, October 30, 2018 6:49 PM