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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
android1
Post Patron
Post Patron

Filter between hours in the day

Hi,

 

This calc column 8AM-10PM = IF(HOUR(vw_PivotChargesFrontPage[Visits_StartTime]) >= 8  && HOUR(vw_PivotChargesFrontPage[Visits_EndTime]) <= 22 ,TRUE(),FALSE())

 

returns all calls with a start time on or after 8am & an end time on or before 10pm. Problem is it also returns calls with an end time between 10pm & 10:59pm. In pic below I do not want 22:55, 22: 50 etc

 

If I add && Minute(vw_PivotChargesFrontPage[Visits_EndTime]) <= 0 then it will exclude calls that end at 8:30am, 19:55 etc 

which I don't want.

 

2018-07-11 13_59_46-Invitation to Tender Call Analysis - Power BI Desktop.jpg

2 ACCEPTED SOLUTIONS

Would need sample data posted that I can copy and paste, just been hacking up to this point.


@ 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

@android1,

 

Add the following Boolean expression as well.

    vw_PivotChargesFrontPage[Visits_StartTime]
        <= vw_PivotChargesFrontPage[Visits_EndTime]
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

8AM-10PM = IF(HOUR(vw_PivotChargesFrontPage[Visits_StartTime]) >= 8  && HOUR(vw_PivotChargesFrontPage[Visits_EndTime]) <= 21 ,TRUE(),FALSE())

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

 

What about calls with an end time of 22:00. They won't be included if I use <=21.

 

I'd like all calls returned with a start time from 8am onwards & with an end time of 22:00 at the latest.

 

Expected results ->

 

2018-07-11 16_16_08-Office Ext  & Mob Numbers Draft  [Read-Only] - Excel.jpg

8AM-10PM = 
IF(
     HOUR(vw_PivotChargesFrontPage[Visits_StartTime]) >= 8  
&&
(
HOUR(vw_PivotChargesFrontPage[Visits_EndTime]) <= 21
||
(
HOUR(vw_PivotChargesFrontPage[Visits_EndTime]) = 22
&&
MINUTE(vw_PivotChargesFrontPage[Visits_EndTime]) = 0)
)
),
TRUE(),
FALSE()
)

Maybe.


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

Oops, inadvertantly accepted as solution.

 

Not quite returning what I need.

 

2018-07-11 20_00_55-Invitation to Tender Call Analysis WIP - Power BI Desktop.png

@android1,

 

Add the following Boolean expression as well.

    vw_PivotChargesFrontPage[Visits_StartTime]
        <= vw_PivotChargesFrontPage[Visits_EndTime]
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Yes, thank you. @Greg_Deckler Your code works perfectly with the added expression from @v-chuncz-msft

Cool! The power of teamwork!


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

Would need sample data posted that I can copy and paste, just been hacking up to this point.


@ 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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