cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amandabus21
Helper II
Helper II

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.

 

amandabus21_0-1664569487148.png

 

Thank you!

1 ACCEPTED SOLUTION

@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.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

@amandabus21 Construct a TimeTable for your Time slicer:

 

HourTable = 
SELECTCOLUMNS(
    ADDCOLUMNS(
        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
  )

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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. 

amandabus21_0-1664811525724.png

 

@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
  )

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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?

@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.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors