Fix DAX for Calculate cross tables

[lovishsood1]

Hi guys,

Here is my DAX Query, for which I have trouble finding the solution.

Sometimes it gives an error on the Leaves table or sometimes random errors anywhere.

EVALUATE CALCULATETABLE( SUMMARIZECOLUMNS( Employees[EmployeeCode], Employees[FullName], DateTable[FormatDate], DateTable[Day Name], "In Time", MIN(PunchInRecords[In_Time]), /*Measure*/ "Out Time", MAX(PunchInRecords[Out_Time]), /*Measure*/ "Punch-In Hours", SUM(PunchInRecords[PunchInHours]), /*Measure*/ "Break Hours", SUM(PunchInRecords[BreakHours]), /*Measure*/ "Work Hours", SUM(PunchInRecords[WorkHours]), /*Measure*/ "Leave Count", SUM(Leaves[LeaveCount]), /*Text Column*/ "Leave Type", CONCATENATEX(VALUES(Leaves[LeaveType]), Leaves[LeaveType], ";"), /*Text Column*/ "Leave Status", CONCATENATEX(VALUES(Leaves[RequestStatus]), Leaves[RequestStatus], ";"), /*Text Column*/ "WFH", SUM(WFH[WFH Count]), /*Text Column*/ "WFH Status", CONCATENATEX(VALUES(WFH[WFH Status]), WFH[WFH Status], ";"), /*Text Column*/ "OT", SUM(Overtime[OT_Count]), "Grant Type", CONCATENATEX(VALUES(Overtime[OT Redeem Type]), Overtime[OT Redeem Type], ";"), /*Text Column*/ "OT Status", CONCATENATEX(VALUES(Overtime[RequestStatus]), Overtime[RequestStatus], ";"), /*Text Column*/ "Color_Condition", MAX(PunchInRecords[newColor_Condition]) /*Measure*/ ), {filter_block} ) ORDER BY {order_column} {sort_dir}

Can anybody help me here?

[lovishsood1 · Accepted solution]

Hi All,

This has been resolved. It was just some data type issues and syntax errors on Table/Column Names.

This DAX is working fine.

[v-sshirivolu]

Hi @lovishsood1 ,
Thanks for the update. I’ve reviewed the logic and here is the version of the new table DAX. This version uses a CROSSJOIN between Employees and DateTable, and then adds the daily values from each activity table. It now returns the expected results for each employee and date.

Base_Output =
VAR BaseTable =
SUMMARIZE(
CROSSJOIN( Employees, DateTable ),
Employees[EmployeeCode],
Employees[FullName],
DateTable[FormatDate],
DateTable[Day Name]
)
RETURN
ADDCOLUMNS(
BaseTable,
"In Time", CALCULATE( MIN( PunchInRecords[In_Time] ) ),
"Out Time", CALCULATE( MAX( PunchOutRecords[Out_Time] ) ),
"Punch Hours",
COALESCE(
CALCULATE( SUM( TimeRecords[TotalHours] ) ),
DIVIDE(
DATEDIFF(
CALCULATE( MIN( PunchInRecords[In_Time] ) ),
CALCULATE( MAX( PunchOutRecords[Out_Time] ) ),
MINUTE
),
60
)
),
"Break Hours", CALCULATE( SUM( BreakRecords[BreakHours] ) ),
"Leave Count",
CALCULATE(
COUNTROWS(
FILTER(
'Leaves',
'Leaves'[EmployeeCode] = SELECTEDVALUE( Employees[EmployeeCode] )
&& 'Leaves'[FormatDate] = SELECTEDVALUE( DateTable[FormatDate] )
)
)
),
"Total Hours", CALCULATE( SUM( TimeRecords[TotalHours] ) )
)

Let me know if you want any help further.

Thank you.

[Praful_Potphode]

Hi @lovishsood1 ,

usually functions like CONCATENATEX,ALL,SUMMARIZECOLUMNS,ALL,ALLEXCEPT,ALLSELECTED cause issues.my suggestion is to investigate them separately.try commenting one measures at a time to isolate the measure causing the real issue.

Please Give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[ValtteriN]

Hi, 

It is adds a bit of difficulty that there is no example data, no example errors and the dax is not formatted well. However, based on your description my first recommendation is that you check that the refecend tables have valid relationships to Employees and DateTable. Another suggestion is to SUMMARIZE + ADDCOLUMNS. Here is an example generated with copilot:

EVALUATE
VAR BaseTable =
    SUMMARIZE(
        Employees,
        Employees[EmployeeCode],
        Employees[FullName],
        DateTable[FormatDate],
        DateTable[Day Name]
    )
RETURN
ADDCOLUMNS(
    BaseTable,
    "In Time", CALCULATE(MIN(PunchInRecords[In_Time])),
    "Out Time", CALCULATE(MAX(PunchInRecords[Out_Time])),
    "Punch-In Hours", CALCULATE(SUM(PunchInRecords[PunchInHours])),
    "Break Hours", CALCULATE(SUM(PunchInRecords[BreakHours])),
    "Work Hours", CALCULATE(SUM(PunchInRecords[WorkHours])),
    "Leave Count", CALCULATE(SUM(Leaves[LeaveCount])),
    "Leave Type", CALCULATE(CONCATENATEX(VALUES(Leaves[LeaveType]), Leaves[LeaveType], ";")),
    "Leave Status", CALCULATE(CONCATENATEX(VALUES(Leaves[RequestStatus]), Leaves[RequestStatus], ";")),
    "WFH", CALCULATE(SUM(WFH[WFH Count])),
    "WFH Status", CALCULATE(CONCATENATEX(VALUES(WFH[WFH Status]), WFH[WFH Status], ";")),
    "OT", CALCULATE(SUM(Overtime[OT_Count])),
    "Grant Type", CALCULATE(CONCATENATEX(VALUES(Overtime[OT Redeem Type]), Overtime[OT Redeem Type], ";")),
    "OT Status", CALCULATE(CONCATENATEX(VALUES(Overtime[RequestStatus]), Overtime[RequestStatus], ";")),
    "Color_Condition", CALCULATE(MAX(PunchInRecords[newColor_Condition]))
)

If you need further help, please add example errors and mock data.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/