Forum Discussion
Using One slicer for 2 fields
Hi,
In my Task Table I have 2 fields [Start Date] and [Finish Date]. When I add a slicer, it only allows me to add 1 slicer which is either for Start Date or for Finish Date. (See link below)
https://www.screencast.com/t/5ZRWsaMvNUt
What I want is to have one slice with both fields. That way i can select a Start Date and a Finish Date, and the data will provide me with tasks that are between the selected Dates. To look something like the following the below link.
https://www.screencast.com/t/NDSBKYa0uXVa
Any Ideas? Would appreciate any help I can get. Been stuck on this for quiet some time.
4 Replies
Hi,
In my Task Table I have 2 fields [Start Date] and [Finish Date]. When I add a slicer, it only allows me to add 1 slicer which is either for Start Date or for Finish Date. (See link below)
https://www.screencast.com/t/5ZRWsaMvNUt
What I want is to have one slice with both fields. That way i can select a Start Date and a Finish Date, and the data will provide me with tasks that are between the selected Dates. To look something like the following the below link.
https://www.screencast.com/t/NDSBKYa0uXVa
Any Ideas? Would appreciate any help I can get. Been stuck on this for quiet some time.
There are a couple of approaches you can take:
- See this article
Reshape your data so that you have a single date column corresponding to each date a task is active.
e.g. if Task A has Start Date = 1/1/2018 and End Date = 5/1/2018, then you end up with something likeTask A 1-Jan-2018 Task A 2-Jan-2018 Task A 3-Jan-2018 Task A 4-Jan-2018 Task A 5-Jan-2018 - Leave your existing data structured as-is (with Start Date & End Date columns), but
- Add a disconnected Date table
- Create an "events in progress" type measure.
There are various articles on this such as:
https://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/
https://blog.crossjoin.co.uk/2013/06/13/a-new-events-in-progress-dax-pattern/
https://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf from page 15 (examples are DAX Queries rather than measures)
Regards,
Owen
- See this article
Hi TodGrindley
The method you've done already is something that many people do and it works it just isn't that intuitive and requires more clicks than should be needed.
What you could do is through the query editor create a new column named "Days Active". Here you would list the days between the start and end date. Your slicer would then be on this field and would only show tasks where they were active on a specific day.
Dates Between
Dates = { Number.From([Start]..Number.From([End]) }Expand that list to new rows and you'll have the days where a task is active. It might be worth duplicating the table before creating the column to use as reference.
Unique Tasks > Task Details (with column)
Hope this helps,
J
Hi TodGrindley,
As of now, it is not possible to add two fileds (start date and end date) into a single slicer. For your requirement, you may submit an idea here.
Best regards,
Yuliana Gu
