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 remov...
  • 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
    )
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
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

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

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

        Enjoy DAX!