topic Re: Help needed with filters in DAX Commands and Tips

Help needed with filters

Anonymous — Thu, 16 Jun 2022 14:35:13 GMT

Hello,

For background - the hire date field has no relationship with the date table.

I need to count employees that were hired in the past year based on the date selected - but I need to remove the filter from the table so I can get all the employees.

Here is what I am trying - but the filter is still in place

------------------------------------------

TTM Hires =

Var selecteddate = max('Date'[Date])

Var enddate = dateadd(lastdate('Date'[Date]),-1,Year)

return

Calculate (DISTINCTCOUNT('Monthly Workforce'[FDS ID]),

REMOVEFILTERS('Date'[Date]),

Filter('Monthly Workforce',

AND(

'Monthly Workforce'[Hire Date] <= selecteddate ,

'Monthly Workforce'[Hire Date]>enddate)

))

Re: Help needed with filters

johnt75 — Fri, 17 Jun 2022 10:46:30 GMT

Try changing the REMOVEFILTERS('Date'[Date]) to REMOVEFILTERS('Date'), so that filters on any column in the date table are removed. Its common with date tables for other filter columns to be added behind the scenes, e.g. a month sort order column.

Re: Help needed with filters

Anonymous — Fri, 17 Jun 2022 12:55:55 GMT

This really has me puzzled. I am quite the beginner when it comes to DAX - but I thought what I was doing might work 😋

Thank you - but the change did not work.

I was able to get the correct answer by creating a calculated table that summarizes the Workforce table by month and then filtering for the needed timeframe. But I am really curious as to why I can't get the other formula to work.

Re: Help needed with filters

johnt75 — Fri, 17 Jun 2022 13:04:25 GMT

Perhaps the FILTER is being called before the REMOVEFILTERS is applied. Try

TTM Hires = VAR selecteddate = MAX ( 'Date'[Date] ) VAR enddate = DATEADD ( LASTDATE ( 'Date'[Date] ), -1, YEAR ) RETURN CALCULATE ( DISTINCTCOUNT ( 'Monthly Workforce'[FDS ID] ), REMOVEFILTERS ( 'Date' ), 'Monthly Workforce'[Hire Date] <= selecteddate && 'Monthly Workforce'[Hire Date] > enddate )

Re: Help needed with filters

Anonymous — Fri, 17 Jun 2022 13:09:22 GMT

Thank you so much. This is such a long journey. (Learning DAX)

Re: Help needed with filters

johnt75 — Fri, 17 Jun 2022 13:16:25 GMT

If you can, take the SQL BI guys course on DAX, and definitely check them out on YouTube.

Enjoy DAX!