Forum Discussion

jharsh's avatar
jharsh
Frequent Visitor
5 years ago
Solved

Filtering Page Visuals from Between Date Slicer Range

I have a requirement to filter all the visuals on a page where the dates for the tables powering them are between the selected date slicer dates. I have created 2 measures on my date table for min and max date.  These are being calculated as

CALCULATE(MIN(DateTable[Date]),ALLSELECTED(DateTable[Date]))

and

CALCULATE(MAX(DateTable[Date]),ALLSELECTED(DateTable[Date]))
 
My Employee table is being used for a stacked bar chart.  I would like to only include Employees that have a termination date between the min and max selected slicer dates.  Because the slicer is being used for multiple visuals I do not have relationships configured from the DateTable to the Employee or other tables. The measures show the correct dates when viewed in a table visual but do not return the expected results when used in a computed column on my bar chart.  
Need Help

3 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    5 years ago

    can you provide more context?  what tables are you using, what is plotted on what.  Sample  data is ideal to understand what is going on here and some pictures are useful too.

    • jharsh's avatar
      jharsh
      Frequent Visitor
      5 years ago

      Sure. For the employee filter. I am using 2 tables DateTable which only has 1 column of dates with a type of date time and the Employee table.  This has Hire Date (datetime), Employee Name, Termination Date (datetime nullable), Active Status (Boolean), and Job Category.  I can not always rely on IsActive as it doesn't change but when changing the date range the employee may "become" active if it falls before termination date.

      DateTable Example

      Employee Table Example for security purposes

       

       

       

      Bar Chart

      The plots are X Axis Job Category Values Count of Employee Names. From the above data examples I would like to only get a result of 4 if the end date of the slicers is 5/25/2021 or after and a count of 5 if it is 5/24/2021 or before.  I have tried using computed column IF(Employee[TerminationDate] >= MinDate && Employee[TerminationDate <= MaxDate,TRUE(),FALSE()) then filtering on Isvalid = true but I keep getting a count of 5 all the time.