Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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! 🙂
Proud to be a Super User!
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! 🙂
Proud to be a Super User!
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |