Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mann
Resolver III
Resolver III

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 IDValuesStart DateEnd Date
11010/01/2019 8:02:00AM10/01/2019 5:00:00PM
12010/02/2019 8:05:00AM10/02/2019 8:05:00PM
13010/03/2019 8:00:00AM10/03/2019 4:04:00PM
24010/01/2019 10:02:00AM10/01/2019 5:00:00PM
21010/02/2019 9:05:00AM10/02/2019 8:05:00PM
22010/03/2019 8:30:00AM10/03/2019 4:04:00PM
33010/01/2019 7:02:00AM10/01/2019 5:00:00PM
41010/02/2019 5:05:00AM10/02/2019 8:05:00PM
32010/03/2019 2:00:00AM10/03/2019 4:04:00PM
41010/01/2019 8:02:00AM10/01/2019 5:00:00PM
42010/02/2019 8:05:00AM10/02/2019 8:05:00PM
53010/03/2019 8:00:00AM10/05/2019 4:04:00PM
64010/01/2019 10:02:00AM10/01/2019 5:00:00PM
71010/02/2019 9:05:00AM10/02/2019 8:05:00PM
82010/05/2019 8:30:00AM10/06/2019 4:04:00PM
83010/01/2019 7:02:00AM10/01/2019 5:00:00PM
91010/02/2019 5:05:00AM10/04/2019 8:05:00PM
102010/04/2019 2:00:00AM10/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.

1 ACCEPTED SOLUTION
JarroVGIT
Resident Rockstar
Resident Rockstar

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 IDValuesStart DateINDEX
11010/01/2019 8:02:00AM1
110 10/01/2019 8:03:00AM1
11010/01/2019 8:04:00AM1

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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
JarroVGIT
Resident Rockstar
Resident Rockstar

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 IDValuesStart DateINDEX
11010/01/2019 8:02:00AM1
110 10/01/2019 8:03:00AM1
11010/01/2019 8:04:00AM1

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! 🙂





Did I answer your question? Mark my post as a solution!

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.