Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
Would need sample data posted that I can copy and paste, just been hacking up to this point.
Add the following Boolean expression as well.
vw_PivotChargesFrontPage[Visits_StartTime] <= vw_PivotChargesFrontPage[Visits_EndTime]
8AM-10PM = IF(HOUR(vw_PivotChargesFrontPage[Visits_StartTime]) >= 8 && HOUR(vw_PivotChargesFrontPage[Visits_EndTime]) <= 21 ,TRUE(),FALSE())
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 ->
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.
Oops, inadvertantly accepted as solution.
Not quite returning what I need.
Add the following Boolean expression as well.
vw_PivotChargesFrontPage[Visits_StartTime] <= vw_PivotChargesFrontPage[Visits_EndTime]
Yes, thank you. @Greg_Deckler Your code works perfectly with the added expression from @v-chuncz-msft
Cool! The power of teamwork!
Would need sample data posted that I can copy and paste, just been hacking up to this point.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |