I’m using trying to create a measure in DAX that measures the the activity of investigator’s in our company working in specific geographical areas and filter it by a range of dates Here is the code I’ve written:

ActiveInvestigators =
CALCULATE(
DISTINCTCOUNT(‘dw factAssignment'[AssignedUserKey]),
FILTER(
‘dw dimCase’,
‘dw dimCase'[CreateDate]<= MAX(‘dw dimDate'[DT]) &&
‘dw dimCase'[CompletedDate]>= MIN(‘dw dimDate'[DT])
)
)

I have three tables that I’m working with: dw FactAssignmenet which is the fact table, dw dimcase and dw dimDate.

I have an existing visual that’s in which I use slicers to filter data using ‘dw dimdate'[DT]. My goal is to filter the distinct assingeduserkeys by a range of dates. The code I have here does that successfully. What I would like to do however, is add a 30 day window prior to the filtered assigned date using DATEADD and I’ve been unable to do that.

I’ve tried a couple things; here are the errors that I’m getting:

ActiveInvestigators =
CALCULATE(
DISTINCTCOUNT(‘dw factAssignment'[AssignedUserKey]),
FILTER(
‘dw dimCase’,
‘dw dimCase'[CreateDate]<= MAXX(DATEADD(‘dw dimDate'[DT],-30,DAY),[DT] &&
‘dw dimCase'[CompletedDate]>= MIN(‘dw dimDate'[DT])
)
))

this one doesn’t work and throws the error message “the function MAXX cannot work with data type boolean.” this is confusing to me because none of the columns I’m working with are boolean.

The next one is this

ActiveInvestigators =
CALCULATE(
DISTINCTCOUNT(‘dw factAssignment'[AssignedUserKey]),
FILTER(
‘dw dimCase’,
‘dw dimCase'[CreateDate]<= MAX(DATEADD(‘dw dimDate'[DT],-30,DAY) &&
‘dw dimCase'[CompletedDate]>= MIN(‘dw dimDate'[DT])
)
))

the error message I get here is “the max function only accepts a column reference as an argument.”

Any suggestions on what to do here?