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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
amandabus21
Helper V
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.

 

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.

 

 



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

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
  )

 

 



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

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.



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

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
  )


Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

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.

 

 



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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