DAX - Calculating at the Line Level (SUMX) with Multiple Filters

Question

What I'm trying to get DAX to do is:

Look across each row in a table of HR data.
Identify the start date of the employee ("employee a")
Sum up the number of other employees in the table with the following filters applied:
a. Successfully completed their assignment
b. Ended their assignment BEFORE the start date + 31
c. Ended their assignment AFTER the start date - 31 (which is to say within a month of employee a's start date)
d. Started before employee a (to not count employee a or anyone in their cohort in the count)
e. Has the same job title as employee a.

This is essentially asking the question in normal English "for each of my employees, how many other employees with the same job title ended their assignments successfully within a month of that employee starting?" and in DAX is basically just "how do I apply multiple filter criteria to a SUMX or COUNTAX measure/calculated column?"

The measure I've already tried is:

Contractors Available = COUNTAX(
'BAT VwRptMspAssignment',
    CALCULATE(
        DISTINCTCOUNT('BAT VwRptMspAssignment'[assignmentgk]),
        FILTER(
            FILTER(
                FILTER(
                    FILTER(
                        FILTER(ALL('BAT VwRptMspAssignment'),
                        'BAT VwRptMspAssignment'[End.Date]<EARLIER('BAT VwRptMspAssignment'[Start.Date])+31),
                    'BAT VwRptMspAssignment'[End.Date]>EARLIER('BAT VwRptMspAssignment'[Start.Date])-31),
                'BAT VwRptMspAssignment'[Start.Date]<EARLIER('BAT VwRptMspAssignment'[Start.Date])),
            'BAT VwRptMspAssignment'[EoaReason]="Successful Completion"),
        'BAT VwRptMspAssignment'[JobPostingTitle.1]=EARLIER('BAT VwRptMspAssignment'[JobPostingTitle.1]))
        )
    )

And the calculated column I tried was:

Contractors Available.1 = SUMX(
FILTER(
        FILTER(
            FILTER(
                FILTER(
                    FILTER(
                        FILTER(ALL('BAT VwRptMspAssignment'),
                            'BAT VwRptMspAssignment'[customergk]=EARLIER('BAT VwRptMspAssignment'[customergk])),
                        'BAT VwRptMspAssignment'[JobPostingTitle.1]=EARLIER('BAT VwRptMspAssignment'[JobPostingTitle.1])),
                        'BAT VwRptMspAssignment'[End.Date]<EARLIER('BAT VwRptMspAssignment'[Start.Date])+31),
                    'BAT VwRptMspAssignment'[End.Date]>EARLIER('BAT VwRptMspAssignment'[Start.Date])-31),
                'BAT VwRptMspAssignment'[Start.Date]<EARLIER('BAT VwRptMspAssignment'[Start.Date])),
            'BAT VwRptMspAssignment'[EoaReason]="Successful Completion"),

'BAT VwRptMspAssignment'[FinishFlag])

but neither of these solutions have worked.
Does anyone have any idea why or what else I can try to accomplish this? An example of the data format, exported to Excel:

"Contractors Available.2" is the calculated column. Note the 521 in the first line. If I apply all of these filters in Excel, it should be zero, this job title is unique in the dataset. It says 107 "Technical Writer - Expert" rows should have ended within a month of 9/26/2017, but these are the only 3 technical writers in the dataset, and zero of the other two ended their assignments within a month of 9/30/2016:

FYI, you can use && / || as logical AND / OR operators instead of nesting a bunch of filters. FILTER(Table, <cond1> && <cond2>) instead of FILTER(FILTER(Table, <cond1>), <cond2>). — Alexis Olson
Thanks Alexis! That works with the calculated column when I specify variables as in Marco Vos's answer, but the problem is with the "EARLIER()" function on multiple columns, which only seems to work when I use nested filters. And I can't specify the row's value as a variable in a measure, only in the calculated column version. — jpford

Marco Vos Marco Vos · Accepted Answer · 2018-09-24T19:38:23

Try something like this for a calculated column:

Contractors Available.1 =
VAR StartDate = 'BAT VwRptMspAssignment'[Start.Date]
VAR JobTitle = 'BAT VwRptMspAssignment'[JobPostingTitle.1]
RETURN
    COUNTROWS (
        FILTER (
            'BAT VwRptMspAssignment',
            'BAT VwRptMspAssignment'[End.Date] < StartDate + 31
                && 'BAT VwRptMspAssignment'[End.Date] > StartDate - 31
                && 'BAT VwRptMspAssignment'[Start.Date] < StartDate
                && 'BAT VwRptMspAssignment'[EoaReason] = "Successful Completion"
                && 'BAT VwRptMspAssignment'[JobPostingTitle.1] = JobTitle
        )
    )

The EARLIER functions are not necessary because the variables will keep the context where they were defined, which is the rowcontext.

EDIT: I tested my formula with the data you provided and it seems to work. I changed the [End.Date] in the second row, to get a result in the first row.

DAX - Calculating at the Line Level (SUMX) with Multiple Filters

1 Answers