Sum values of an employee only when he doesnt have X value in the table for a range of dates

[ixtilion]

Hello,

I've been trying to figure this one out but its proving to be a bit tough...

What I need to do in DAX is a sum of the "Value" column only when the CHECK1 and CHECK2 columns for that employee are set to 0 in all the other rows for the selected Date range.

This is an example dataset:

EMPLOYEE ID	VALUE	CHECK	CHECK2	DATE
1	1	0	0	01/01/2019
1	1	0	0	02/01/2019
1	1	0	0	03/01/2019
1	1	0	0	04/01/2019
1	1	0	0	05/01/2019
1	1	0	0	06/01/2019
2	1	1	0	03/01/2019
2	1	0	0	04/01/2019
2	1	0	0	05/01/2019
2	1	0	0	06/01/2019
3	1	0	0	01/06/2019
3	1	0	1	02/06/2019

• In this case, when selecting a date range from day 1 to day 6, the measure should just return 6 (the sum of all the values for employee 1). Employees 2 and 3 wouldnt be accounted for as they have one of their rows set as 1 in the check columns.
• If I selected a date range from day 5 to day 6, it should return 4 (2 values from employee1, and 2 values from employee2)

Thanks,

if I can provide any more info or explain the issue any further I'd be glad to do so

[sturlaws · Accepted solution]

Hi @ixtilion,

I got this code to work on your sample data

Measure =
VAR _t =
    ADDCOLUMNS (
        VALUES ( 'Table'[EMPLOYEE ID] );
        "c1"; CALCULATE ( SUM ( 'Table'[CHECK] ) );
        "c2"; CALCULATE ( SUM ( 'Table'[CHECK2] ) )
    )
VAR _tt =
    CALCULATETABLE (
        VALUES ( 'Table'[EMPLOYEE ID] );
        FILTER ( 'Table'; ( 'Table'[EMPLOYEE ID]; 0; 0 ) IN _t )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[VALUE] );
        FILTER ( 'Table'; ( 'Table'[EMPLOYEE ID] ) IN _tt )
    )

Regards,

S

[ixtilion]

@sturlaws thanks a lot, it works perfectly and I learned something about using variables and tables in DAX expressions!

Im going to try it out in a real enviroment with a lot of rows and see how it performs, thanks for the insight 🙂