How to filter a report using time

[jdb9294]

I have a table that includes incidents and captures the date and time of the incident.

I have a report that counts various things related to that incident.

I want to use a slicer to filter this report by date and time.

For example, I want to see all the incidents that happened between 1/1/2019 0800 to 01/07/2019 1600.  I would want all incidents that occurred from that start date\time to the end.  So an incident on 1/2/2019 0700 would be included.

I have included a screenshot to show how my data is currently stored.

[v-juanli-msft · Accepted solution]

Hi @jdb9294 

In Edit queries, create two tables

Date table, code in Advanced editor

let 
    startdate=#date(2019,1,1),
    today=DateTime.Date(DateTime.LocalNow()),
    length=Duration.Days(today-startdate),  
    Source = List.Dates(startdate,length,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}})
in
    #"Renamed Columns"

Time table

let
    Source = List.Times(#time(0,0,0),24,#duration(0,0,60,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "time"}}),
    #"Inserted Hour" = Table.AddColumn(#"Renamed Columns", "Hour", each Time.Hour([time]), Int64.Type)
in
    #"Inserted Hour"

Close&&apply

Create a measure and add it into the visual level filter

Measure =
IF (
    MIN ( 'Date'[Date] ) < MAX ( 'Table'[call date] )
        && MAX ( 'Table'[call date] ) < MAX ( 'Date'[Date] ),
    1,
    IF (
        (
            MAX ( 'Table'[call date] ) = MIN ( 'Date'[Date] )
                && HOUR ( MAX ( 'Table'[call time] ) ) >= MIN ( 'Time'[Hour] )
        )
            || (
                MAX ( 'Table'[call date] ) = MAX ( 'Date'[Date] )
                    && HOUR ( MAX ( 'Table'[call time] ) ) <= MAX ( 'Time'[Hour] )
            ),
        1,
        0
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ChrisMendoza]

@jdb9294 -

Pretty sure the best practice is to use a Time Dimenison table related to your [CallTime]. https://www.biinsight.com/time-dimension-in-power-bi-and-ssas-tabular-model-supporting-minutes-time-... shows how you can create a table.

[jdb9294]

Thanks for the link.  I have the table created and related to my call time, but I am still not sure how to get a slider to do what I want it to do here...

[ChrisMendoza]

@jdb9294 -

I put this mock-up together, which part are you getting stuck at?

[jdb9294]

Thank you so much for this help by the way!

Let's use your last screenshot, I think the problem I still have is let's say there is a record with a date of 11/14/2019 at 0900. Will that be picked up?

It seems you are only seeing calls that occurred between the hours of 1700 and 1859 on each day.

[ChrisMendoza]

@jdb9294 -

Ah, I see what you mean. I've never really had a need for a dimTime so I'm really just learning alongside you.  I found this https://www.blue-granite.com/blog/disconnected-table-power-bi and thought about using the SELECTEDVALUE to find out what is selected in the slicer, haven't quite got anything really going. The idea would be to find the minimum datetime stamp and the maximum datetime stamp then getting the results inbetween. I don't know if its the best way, it's just the first thing I thought about.