Forum Discussion

Scientist324's avatar
Scientist324
Regular Visitor
2 years ago
Solved

Date Slicer Modifying Employee List Based on Hire/Termination Date

Looking for some guidance on how to make employee names appear or disappear based on the date slicer chosen. In other words, can the date slicer change the employee slicer to who was "employed" during that time period. Would appreciate anyone's help on this.  I have the hire dates, termination dates and have that linked to my date table already. 

Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    That's a standard "measure as visual filter"  pattern.  Create a measure that returns 1 for a given employee and date if the date falls between hire and termination, and 0 otherwise.  Then in the visual where you list the employees set the visual filter to the measure being equal to 1.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Scientist324 

     

    Thank you very much lbendlin for your prompt reply. Allow me to add some details.

     

    “Employee”

     

     

    Create a measure. Determine if the employee is employed within the scope of the slicer.

     

    Measure = 
var _mindate = MIN('Date'[Date])
var _maxdate = MAX('Date'[Date])
var _hiredate = SELECTEDVALUE('Employee'[hire dates])
var _terminationdate = SELECTEDVALUE('Employee'[termination dates])
RETURN
IF(
    (_hiredate >= _mindate && _hiredate <= _maxdate)
    && 
    (_terminationdate <= _maxdate && NOT(ISBLANK(_terminationdate))) ,
    1, 
    0
)

     

     

     

    Here is the result.

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    That's a standard "measure as visual filter"  pattern.  Create a measure that returns 1 for a given employee and date if the date falls between hire and termination, and 0 otherwise.  Then in the visual where you list the employees set the visual filter to the measure being equal to 1.

    • Scientist324's avatar
      Scientist324
      Regular Visitor
      2 years ago
      Tried this measure and I am getting an error from using two different tables.  

      IF      (('Employee Table'[Hire Date])<(MIN('Calendar'[Date])&&('Employee Table'[Terminate Date])>(MAX('Calendar'[Date]),"1","0")))
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Scientist324 

     

    Thank you very much lbendlin for your prompt reply. Allow me to add some details.

     

    “Employee”

     

     

    Create a measure. Determine if the employee is employed within the scope of the slicer.

     

    Measure = 
var _mindate = MIN('Date'[Date])
var _maxdate = MAX('Date'[Date])
var _hiredate = SELECTEDVALUE('Employee'[hire dates])
var _terminationdate = SELECTEDVALUE('Employee'[termination dates])
RETURN
IF(
    (_hiredate >= _mindate && _hiredate <= _maxdate)
    && 
    (_terminationdate <= _maxdate && NOT(ISBLANK(_terminationdate))) ,
    1, 
    0
)

     

     

     

    Here is the result.

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • Scientist324's avatar
      Scientist324
      Regular Visitor
      2 years ago

      This measure does not produce an error but it does not the msaure from 0 to 1 when I change the date slider (or visa versa) Any thoughts?