Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help needed with filters

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)


))
  • johnt75's avatar
    johnt75
    4 years ago

    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
    )

5 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        4 years ago

        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
    )