Reputation: 83
What I'm trying to get DAX to do is:
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:
Upvotes: 0
Views: 4213
Reputation: 2968
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.
Upvotes: 1