Forum Discussion
Multiple Filter Conditions in a Measure
I am trying to calculate an employee count based on these conditions:
TermDate is blank or (TermDate > 4/1/2019 and Term Date <= 6/30/2019)
I have tried many combinations and it's not working. Here is my what I currently have the count is not correct.
TEST2 =
CALCULATE ( DISTINCTCOUNT(ResourceAllocationFact[EmployeeID]),
FILTER(EmployeeDim, EmployeeDim[TermDate]= blank()
|| EmployeeDim[TermDate]> 4/1/2019
|| EmployeeDim[TermDate]<=6/30/2019)
CALCULATE ( DISTINCTCOUNT(ResourceAllocationFact[EmployeeID]),
FILTER(EmployeeDim, EmployeeDim[TermDate]= blank()
|| EmployeeDim[TermDate]> 4/1/2019
|| EmployeeDim[TermDate]<=6/30/2019)
)
- Hi Anonymous
The || means OR on DAX the AND syntax is made by && so believe that you should rephrase your measure to:
TEST2 =
CALCULATE (
DISTINCTCOUNT ( ResourceAllocationFact[EmployeeID] ),
FILTER (
EmployeeDim,
EmployeeDim[TermDate] = BLANK ()
|| ( EmployeeDim[TermDate] > 4 / 1 / 2019
&& EmployeeDim[TermDate] <= 6 / 30 / 2019 )
)
)
Regards,
MFelix
2 Replies
Give this a try.
TEST2 = CALCULATE ( DISTINCTCOUNT(ResourceAllocationFact[EmployeeID]), FILTER(EmployeeDim, OR(EmployeeDim[TermDate]= blank(), AND(EmployeeDim[TermDate]> 4/1/2019, EmployeeDim[TermDate]<=6/30/2019 ) ) ) )IF this doesn't work, can you outline specifically what criteria you need with some sample data and an example of the desired outcome.
- Hi Anonymous
The || means OR on DAX the AND syntax is made by && so believe that you should rephrase your measure to:
TEST2 =
CALCULATE (
DISTINCTCOUNT ( ResourceAllocationFact[EmployeeID] ),
FILTER (
EmployeeDim,
EmployeeDim[TermDate] = BLANK ()
|| ( EmployeeDim[TermDate] > 4 / 1 / 2019
&& EmployeeDim[TermDate] <= 6 / 30 / 2019 )
)
)
Regards,
MFelix
