Forum Discussion
Using Slicer Value to Filter out Ranges of Data
Hi,
I am trying to set up an HR report where the user would select a report date (9/30,10/31) and get the active employees.
My problem is that I can capture the selected value of the slicer but I can't get it to compare to the termination dates so that if the report date was prior to the termination date it would still show as active.
My data looks like this...
| Employee | Hire Date | Termination date |
| 1 | 4/1/2020 | 8/1/2022 |
| 2 | 1/1/2019 | |
| 3 | 6/1/2022 | 11/1/2022 |
| 4 | 12/1/2018 | |
And the slicer would just be a dropdown
| Report Date |
| 6/1/22 |
| 7/1/22 |
| 8/1/22 |
| 9/1/22 |
| 10/1/22 |
| 11/1/22 |
12/1/22
|
Basically on a report date of 7/1/22 I would want to show 4 employees and then on 8/1 it would drop to 3.
Any insight would be greatly appreciated.
Hi Anonymous ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create a measure.
Measure = IF(SELECTEDVALUE('Slicer Table'[Report Date])<MAX('Table'[Termination date]) || MAX('Table'[Termination date])==BLANK(),"active","no active")(3)Set up filtering in the table visual.
(4) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi!
HR data is tricky since you have both hire and termination dates that needs to be taken into account. You need to create two relationships, one between Hire Date and your date table marked as active, and one between Termination Date and your date table markes inactive, plus some measures using USERELATIONSHIP()
There's a good article here with examples of measures that might be of use to you: HR Analytics - Active Employee, Hire and Terminati... - Microsoft Power BI Community
Hope this helps!Hi Anonymous ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create a measure.
Measure = IF(SELECTEDVALUE('Slicer Table'[Report Date])<MAX('Table'[Termination date]) || MAX('Table'[Termination date])==BLANK(),"active","no active")(3)Set up filtering in the table visual.
(4) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
