Forum Discussion
Filtering duration in a table with multiple date selection
Hi Guys,
Need your expertise in this:
I have a Duration table with start date and end date with datetime format as shown:
| Ind ID | Values | Start Date | End Date |
| 1 | 10 | 10/01/2019 8:02:00AM | 10/01/2019 5:00:00PM |
| 1 | 20 | 10/02/2019 8:05:00AM | 10/02/2019 8:05:00PM |
| 1 | 30 | 10/03/2019 8:00:00AM | 10/03/2019 4:04:00PM |
| 2 | 40 | 10/01/2019 10:02:00AM | 10/01/2019 5:00:00PM |
| 2 | 10 | 10/02/2019 9:05:00AM | 10/02/2019 8:05:00PM |
| 2 | 20 | 10/03/2019 8:30:00AM | 10/03/2019 4:04:00PM |
| 3 | 30 | 10/01/2019 7:02:00AM | 10/01/2019 5:00:00PM |
| 4 | 10 | 10/02/2019 5:05:00AM | 10/02/2019 8:05:00PM |
| 3 | 20 | 10/03/2019 2:00:00AM | 10/03/2019 4:04:00PM |
| 4 | 10 | 10/01/2019 8:02:00AM | 10/01/2019 5:00:00PM |
| 4 | 20 | 10/02/2019 8:05:00AM | 10/02/2019 8:05:00PM |
| 5 | 30 | 10/03/2019 8:00:00AM | 10/05/2019 4:04:00PM |
| 6 | 40 | 10/01/2019 10:02:00AM | 10/01/2019 5:00:00PM |
| 7 | 10 | 10/02/2019 9:05:00AM | 10/02/2019 8:05:00PM |
| 8 | 20 | 10/05/2019 8:30:00AM | 10/06/2019 4:04:00PM |
| 8 | 30 | 10/01/2019 7:02:00AM | 10/01/2019 5:00:00PM |
| 9 | 10 | 10/02/2019 5:05:00AM | 10/04/2019 8:05:00PM |
| 10 | 20 | 10/04/2019 2:00:00AM | 10/06/2019 4:04:00PM |
I created a Date table for filtering this duration table as:
DateFilter=
CALENDAR (
MIN ( Duration[Start Date] ),
MAX ( Duration[End Date] )
)
I need to filter the duration table using Datefilter slicer. So one can select a single value or multiple values like:
Single value: 10/01/2019 or 10/03/2019 etc.
Multiple values can be: 10/01/2019 & 10/03/2019 etc.
I need to use filtered results further in SUMMARIZE dax function which is based on duration table and getting used in a measure.
How can I filter Duration table with Date filter( single selection or multiple selection)??
Please advice
Mann.
Working with durations can be tricky. What I often do (as I learned a few months ago on this community) is to create a table that contains all possible rows. First step is to add an index column to the orignal table, then create a calculated table like this:
Ind ID Values Start Date INDEX 1 10 10/01/2019 8:02:00AM 1 1 10 10/01/2019 8:03:00AM 1 1 10 10/01/2019 8:04:00AM 1 etc right upto 10/01/2019 5:00:00PM. This does result in a lot of rows however.
Next is to create a 1-many relationsship from orignalTable[INDEX] to newTable[INDEX] and the Stat Date column and your datetable.
Then you create a slicer with you date table and add the columns from the orignalTable to a visual (matrix or table for example). These then should be filtered 🙂
To generate the table above, have a look at this post here:
Re: How to calculate time between two changing status
Let me know if this works!
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
2 Replies
Working with durations can be tricky. What I often do (as I learned a few months ago on this community) is to create a table that contains all possible rows. First step is to add an index column to the orignal table, then create a calculated table like this:
Ind ID Values Start Date INDEX 1 10 10/01/2019 8:02:00AM 1 1 10 10/01/2019 8:03:00AM 1 1 10 10/01/2019 8:04:00AM 1 etc right upto 10/01/2019 5:00:00PM. This does result in a lot of rows however.
Next is to create a 1-many relationsship from orignalTable[INDEX] to newTable[INDEX] and the Stat Date column and your datetable.
Then you create a slicer with you date table and add the columns from the orignalTable to a visual (matrix or table for example). These then should be filtered 🙂
To generate the table above, have a look at this post here:
Re: How to calculate time between two changing status
Let me know if this works!
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Hi JarroVGIT
Thanks for this clean solution. I think for this requirement one calculated table is required.
Just want to confirm one thing:
The code for Genrate(Table, Generateseries(_StartDate,_EndDate,Time(0,1,0))) will be breaking the main table rows from start time to end time as per time interval row by row right?
Mann
