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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.