topic Re: DAX - Filter Multiple Conditions - Not Counting Rows Correctly in DAX Commands and Tips

DAX - Filter Multiple Conditions - Not Counting Rows Correctly

DH3612 — Fri, 05 Jan 2024 11:47:46 GMT

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?

Re: DAX - Filter Multiple Conditions - Not Counting Rows Correctly

DH3612 — Fri, 05 Jan 2024 14:01:10 GMT

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.

Re: DAX - Filter Multiple Conditions - Not Counting Rows Correctly

sjoerdvn — Fri, 05 Jan 2024 14:59:32 GMT

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?

Re: DAX - Filter Multiple Conditions - Not Counting Rows Correctly

DH3612 — Fri, 05 Jan 2024 16:35:11 GMT

Hi,

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

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.

Re: DAX - Filter Multiple Conditions - Not Counting Rows Correctly

sjoerdvn — Mon, 08 Jan 2024 11:02:28 GMT

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]) )

Re: DAX - Filter Multiple Conditions - Not Counting Rows Correctly

DH3612 — Tue, 09 Jan 2024 15:19:58 GMT

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