I need to transfer this calculation into a table as the dashboard has become too slow:
Overtime =
VAR currentEmpID = MAX ( 'Timesheet Data'[Employee ID])
VAR currentLDW = MAX ( 'Timesheet Data'[last_day_of_week])
VAR currentContractHours = MAX ( 'Timesheet Data'[Work_Week_Hours] )
VAR OT =
CALCULATE (
SUM ( 'Timesheet Data'[Hours] ),
FILTER (
ALL ( 'Timesheet Data'[Employee ID], 'Timesheet Data'[last_day_of_week] ),
'Timesheet Data'[Employee ID] = currentEmpID
&& 'Timesheet Data'[last_day_of_week] = currentLDW
)
) - currentContractHours
RETURN OT
THe way I planned to do it is to place the 3 VAr statements into a table:
VAR currentEmpID = MAX ( 'Timesheet Data'[Employee ID])
VAR currentLDW = MAX ( 'Timesheet Data'[last_day_of_week])
VAR currentContractHours = MAX ( 'Timesheet Data'[Work_Week_Hours] )
--------------------------------------------------------
And I am trying this:
Calculated_times =
SUMMARIZE(
ADDCOLUMNS(
'rep_timesheet_data',
"currentEmpID", CALCULATE (
MAX( rep_timesheet_data[Employee ID]),
FILTER (rep_timesheet_data, rep_timesheet_data[Employee ID] = "currentEmpID" )
),
"currentLDW", CALCULATE (
MAX ( rep_timesheet_data[last_day_of_week]),
FILTER (rep_timesheet_data, rep_timesheet_data[last_day_of_week] = "currentLDW" )
),
"OT", CALCULATE (
SUM( rep_timesheet_data[Work_Week_Hours]),
FILTER (rep_timesheet_data, rep_timesheet_data[Work_Week_Hours] = "OT" )
)
),
[currentEmpID],
[currentLDW],
[OT]
)
THe mistake I am getting is:
DAX comparison operations do not support comparing values of type Number with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.
So [Employee ID] is a text format, while [last_day_of_week] is a date and [Work_Week_Hours] are decimals.
Like the DAX error message says, I could potentially add a format to Employee. Apart from not being sure where to place the FORMAT, I wonder whether I would not get issues along the lines when wanting to use the calculated variables with my main table?
Is there a better way to create a new table with the above three variables?
