Forum Discussion
Post PatronCan I filter two values based on slicer selection?
I have looked and looked and can't find a solution.
I need a headcount of employees as of the last day of the month. It works if I manually set the filters to
Hire Date on or before 9/30/2017 (last day of the month)
Last Date worked after 9/30/2017
If I have a slicer of the last date of each month - is there a way to select different months and have the matrix update or do I have to manually change the filter?
The answer is yes but your post is a little light on details on how best to advise you on how to achieve exactly what you are looking for.
A date dimension table is likely going to be your best solution.
https://www.agilebi.com.au/blog/power-bi-date-dimension
You link this table to your other tables via their date column. Now you set your date slicers to use a column in your date table. A field in your date table that would help you, would be a column called Month Year, which displays the Month and Year. That way if you select "October 2017", the date table itself will be filtered for all dates within October 2017. This context can inform your measures to have them return the correct information.
4 Replies
The answer is yes but your post is a little light on details on how best to advise you on how to achieve exactly what you are looking for.
A date dimension table is likely going to be your best solution.
https://www.agilebi.com.au/blog/power-bi-date-dimension
You link this table to your other tables via their date column. Now you set your date slicers to use a column in your date table. A field in your date table that would help you, would be a column called Month Year, which displays the Month and Year. That way if you select "October 2017", the date table itself will be filtered for all dates within October 2017. This context can inform your measures to have them return the correct information.
kattleesThanks Ross... but there are two dates.. HireDate and LastDateWorked.. Do I do a date table for each one and have two slicers?
Example would be
Name Hiredate LastDateWorked
Kathy 12/3/2012
Sue 7/16/2014 10/15/2017
Billy 2/19/2013 11/05/2017
Rhonda 11/01/2017
In this example, Kathy and Billy would be counted (Hire date on or before 10/31/2017 and LastDateWorked is Blank OR after 10/31/2017
Sue and Rhonda would not be counted.Sue because her last date worked is before 10/31/2017 and Rhonda because her Hiredate is after 10/31/2017
So my headcount for October 2017 would be 2. September 2017 would be 3. November 2017 would be 2
Thanks kattlees, now looking at the data, i can see you have your HireDate and Termination data within the same data source. This does change the approach. Instead we should not link the Date table directly to your Employee table, we will use date details to form our measure. This would be something like:
Headcount = VAR startDate = FIRSTDATE(DateTable[Date]) VAR endDate = LASTDATE(DateTable[Date]) RETURN CALCULATE( DistinctCount(Employees[Name]), Employee[HireDate] <= endDate, OR( ISBLANK(Employee[LastDateWorked]), Employee[LastDateWorked] >= startDate ) )
Now if you put that into a table, displaying month by month from the Date Table, you will get a headcount that is correct for that month.
If you use a date slicer, using the Date Table, you could pick any given date range and get the headcount for that range.