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...
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