Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |