cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

## Date and Time Filters

Hi,

Im having trouble filtering between a certain range of dates, and then after, filtering a certain range of hours.

I need all the incidents from 3/1/22 and 9/30/22. (That I can do with the sclicer)

Next, I need incidents only from 6:00 PM - 7:00AM between 3/1/22 - 9/30/22.

Thank you!

1 ACCEPTED SOLUTION
Super User

@amandabus21 Meant to be used in the Filters pane actually. The intent was to slice using your TimeTable. However, you may need two time tables, one to set a min and one to set a max. And, if you need to go from one day to the next (PM -> AM) then you will probably need to tweak the measure selector accordingly.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
7 REPLIES 7
Super User

@amandabus21 Construct a TimeTable for your Time slicer:

``````HourTable =
SELECTCOLUMNS(
GENERATESERIES(1,24,1),
"Hour",TIME([Value],0,0)
),
"Hour",[Hour]
)``````

Then create a Complex Selector like:

``````Time Selector Measure =
VAR __Opened = MAX('Table'[Date Opened])
VAR __MaxTime = MAX('TimeTable'[Hour])
VAR __MinTime = MIN('TimeTable'[Hour])
VAR __Time = __Opened - TRUNC(__Opened)
RETURN
SWITCH(TRUE(),
__Time >= __MaxTime, 1
__Time <= __MinTime && __Time >= 0, 1,
0
)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper V

@Greg_Deckler do i make the TimeTable as a column or a measure?

Super User

@amandabus21 As a table. In Report view in the Desktop, choose the Modeling tab and then New table and paste in that formula.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper V

Hi Greg, thanks for your help. @Greg_Deckler

I was able to create the table but now when I create the measure I am getting a syntax error.

Super User

@amandabus21 Whoops, missed a comma after the first SWITCH condition:

``````Time Selector Measure =
VAR __Opened = MAX('Table'[Date Opened])
VAR __MaxTime = MAX('TimeTable'[Hour])
VAR __MinTime = MIN('TimeTable'[Hour])
VAR __Time = __Opened - TRUNC(__Opened)
RETURN
SWITCH(TRUE(),
__Time >= __MaxTime, 1,
__Time <= __MinTime && __Time >= 0, 1,
0
)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper V

@Greg_Deckler thank you.

Now how can I use that measure to filter the time between 6PM - 7AM?

Should I make a new column or is it supposed to be used a slicer?

Super User

@amandabus21 Meant to be used in the Filters pane actually. The intent was to slice using your TimeTable. However, you may need two time tables, one to set a min and one to set a max. And, if you need to go from one day to the next (PM -> AM) then you will probably need to tweak the measure selector accordingly.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors