Dax - Ignores filter Condition

DH3612 — Mon, 12 Feb 2024 10:01:05 GMT

I have a piece of code in DAX that "works" perfectly. However if I add an extra filter inito the code, then it goes a bit pear shaped. For the life of me I ave tried to fix this and failed miserably. Any suggestions welcome 🙂

The code below. This works perfect - counting staff at month end each month. BUT, only if no slicers are active to filter "Business Area".

EmployeesAtEndOfMonth = IF( ISFILTERED('staffmaster'[Business Area]), CALCULATE( DISTINCTCOUNT('staffmaster'[Unique ID]), FILTER( ALL('staffmaster'), 'staffmaster'[Business Area] IN VALUES ('STAFFMASTER'[Business Area]) && ('staffmaster'[End Date] >= MAX('Date'[End of Month]) || ISBLANK('staffmaster'[End Date])) && 'staffmaster'[Start Date] <= MAX('Date'[End of Month]) ) ), CALCULATE( DISTINCTCOUNT('staffmaster'[Unique ID]), FILTER( ALL('staffmaster'), ('staffmaster'[End Date] >= MAX('Date'[End of Month]) || ISBLANK('staffmaster'[End Date])) && 'staffmaster'[Start Date] <= MAX('Date'[End of Month]) ) ) )

If I select a "Business Area" in a slicer. Lets for example say "HR", then I get some odd behavour. If the month in question did not have someone new starting that month, it doesnt count anything for that month.

Example -
Jan23 - 30 staff (Powerbi counts 30 staff because someone new started month)

Feb 23 - 37 staff (because at least one new start that month)
Mar23 - 0 staff (no new starts this month so powerbi just counts the month as blank)

April 23 - 35 staff (at least one new start)

May23 - 0 staff (no new starts this month)

Jun23 - 36 staff (at least one new start)

So the only correlation between the blanks is that we had no new starts in "HR" in the blank months. I have check against other Business Areas and the correlation seems to hold up. ANy business area where we had a least one new start each month, has no blanks. Any business area where we had no new starts in any month, those months show a blank.

Im no PBI master and I expect the answer is simple enough, but im stumped.

As for data tables the data comes from:

Date Table - marked as date table, called "Date". Has columns for [Date], [End of Month]

Staff table - called Staffmaster. Has columns for [Unique ID], [Start Date], [End Date], [Business Area].

All i want to do is show a number of staff employed on the last day of each month, and be able to filter it by business area.

Re: Dax - Ignores filter Condition

DH3612 — Mon, 12 Feb 2024 16:12:15 GMT

OK, I have no idea why it was counting that way. And I have rewritten the entire piece to use amitchandak code from his youtube page. It works perfect under all tests now.

Amits Youtube Linky

topic Dax - Ignores filter Condition in DAX Commands and Tips

Dax - Ignores filter Condition

Re: Dax - Ignores filter Condition