Calculate function in a measure breaks cross-table filtering?


#1

I’m sure this is simple, but it’s driving me crazy.

I have a simple set of measures that calculate the number of employee records based on various criteria. First, all records:
All Employee Records = COUNTROWS(‘Employee Master’)

Then just active records:
All Active Emp Records = CALCULATE([All Employee Records], ‘Employee Master’[Active Status] = “A”)

Then I created a measure that removes additional types of employees using the field “Group”, which I want to use as my main measure on this report:
Employees = CALCULATE([All Active Emp Records],‘Employee Master’[Group] <> “5555”, ‘Employee Master’[Group] <> “9999”)

This gets me what I want, but it breaks crossfiltering between tables on my report using the “Group” field. I should be able to click on “EXEM” in the group table and have it filter the table above to show only those 401 employees and whatever their status is. instead, it has no effect. Every other table works (I have a dozen based on different demographic statuses, all showing current employee counts using the “Employees” measure detailed above, so it has to be something with using the “Group” field in the measure. Why can’t I do that?

image

Thanks for the help.


#3

Thanks for your patience on this one.

I believe the issue is the way this is written inside the the CALCULATE function.

Instead of the , can you try using ||

It could also be relationship based between two of your tables.

If it’s only occurring between two specific table and nothing else, there will be something in the relationship that isn’t working I would say.

Can you try these things and let us know if it works out.

Thanks