DAX - Filter Multiple Conditions - Not Counting Rows Correctly

[DH3612]

Hey all. Hope you can help with this one. Ive tried and tried and not really got anyweher useful. Even tried ChatGPT but after that failed I reverted back to my original doc. 

I have a PBI Desktop file. It contains the following Tables/Columns

Staffmaster (Start Date, End Date, Unique ID, ELT) This is the main data tablewith all employees on it. 

EOM (End of Month) This is a table with just the end date of every month for the last 30 years

Date (Date, Month, year) Date Table and noted as such in PBI

I currently use this code to count my active employees at each month end. This then goes into a column chart showing how many employees on each month end date.

EmployeeCount = 
CALCULATE(
    COUNTROWS(RELATEDTABLE(Staffmaster)),
    FILTER(
        Staffmaster,
        Staffmaster[Start Date] <= EARLIER(EOM[End of Month]) &&
        (ISBLANK(Staffmaster[End Date]) || Staffmaster[End Date] > EARLIER(EOM[End of Month]))
    )
)

I want to add a way to have an additional filter condition on the ELT Column. Using a slicer on the ELT column. Basically break it down by Business Areas. But everything I have tried has failed. Most of which has been along the lines of the following. 

ActiveEmployeeCount =
VAR SelectedELTValues = VALUES(Staffmaster[ELT])

RETURN
CALCULATE(
    COUNTROWS(Staffmaster),
    FILTER(
        Staffmaster,
        Staffmaster[Start Date] <= MAX(EOM[End of Month]) &&
        (ISBLANK(Staffmaster[End Date]) || Staffmaster[End Date] > MIN('EOM'[End of Month])) &&
        Staffmaster[ELT] IN SelectedELTValues
    )
)

However this just seems to count all rows in the staffmaster table without filtering. Variations I tried seem to either count all rows, or count a singular value of 1 for each month end. 

Any suggestions for where I am going wrong?

[sjoerdvn]

You shouldn't have to add any reference to dimension columns (in this case Staffmaster[ELT]) in your measure if you simply want to filter or slice. That's the concept of Power BI...
Have you tried taking out that logic?

[DH3612]

Hi, 

The basic End of Month Employed Staff Calc is as follows:

EmployeeCount = 
CALCULATE(
    COUNTROWS(RELATEDTABLE(Staffmaster)),
    FILTER(
        Staffmaster,
        Staffmaster[Start Date] <= EARLIER(EOM[End of Month]) &&
        (ISBLANK(Staffmaster[End Date]) || Staffmaster[End Date] > EARLIER(EOM[End of Month]))
    )
)

If I add a slicer to the page, then select an ELT/Business Area then it does not affect the visual in any way. (It affects thers on the page bvut not the Employee Count). I just need a way to filter that result further by ELT. 

[sjoerdvn]

I am starting to suspect  you have added "EmployeeCount" as a computed column instead of as a measure. Computed column values are calculated at load, so will never work with slicers.

I created a measure like below, and that works fine.

EmployeeCount = 
VAR sd = MAX(EOM[End of Month])
RETURN CALCULATE(
    COUNTROWS(Staffmaster)
    ,StaffMaster[Start Date]<=sd
    ,StaffMaster[End Date]>=sd || ISBLANK(StaffMaster[End Date])
)

[DH3612]

ahh, nice catch. I will try that when I get some time tonight and respond. That makes sense, I am sure you're correct.

[DH3612]

OK, I have been trying again with this and still getting nowhere:

If I use the code as follows - with a name or Business Area hard coded, then it works perfect. 

EmployeeCount = 
CALCULATE(
  COUNTROWS(Staffmaster),
  FILTER(
    Staffmaster,
    Staffmaster[Start Date] <= EOMONTH(EOM[End of Month], 0) &&
    (ISBLANK(Staffmaster[End Date]) || Staffmaster[End Date] > EOMONTH(EOM[End of Month], -1))
  ),
  Staffmaster[ELT] = "Bob Smith"
)

However if I change it to use a slicer dynamically as such. I get a value 1 for every month. 

EmployeeCount = 
CALCULATE(
  COUNTROWS(Staffmaster),
  FILTER(
    Staffmaster,
    Staffmaster[Start Date] <= EOMONTH(EOM[End of Month], 0) &&
    (ISBLANK(Staffmaster[End Date]) || Staffmaster[End Date] > EOMONTH(EOM[End of Month], -1))
  ),
  Staffmaster[ELT] = SELECTEDVALUE(Staffmaster[ELT])
)