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.

Regular Visitor

## Time Filter Not Working

Hi,

In another thread , Creating Time Filter, I was looking for a solution to filter First Response Time (Mins) according to create date

I wanted to know the average First response time (mins) that were created during working hours 08:00 - 17:00

and average First response time (mins) that were created outside working hours 17:01 - 7:59

In my previous thread, @v-tangjie-msft, provided a solution by creating a measure

During Working Hours

``````average(8:00-17:00) = var _a=CALCULATE(SUM('Table'[First Response (Minute)]),FILTER('Table', 'Table'[Create Time]<=TIME(17,00,00) && 'Table'[Create Time]>=TIME(8,00,00)))
var _b=COUNTROWS(FILTER('Table','Table'[Create Time]<=TIME(17,00,00) && 'Table'[Create Time]>=TIME(8,00,00)))

return
DIVIDE(_a,_b,0)``````

Outside Working Hours

``````average (17:01-7：59) = var _c=CALCULATE(SUM('Table'[First Response (Minute)]),FILTER('Table', 'Table'[Create Time]>=TIME(17,01,00) || 'Table'[Create Time]<=TIME(7,59,00)))
var _d=COUNTROWS(FILTER('Table','Table'[Create Time]>=TIME(17,01,00) || 'Table'[Create Time]<=TIME(7,59,00)))
return
DIVIDE(_c,_d,0)``````

It was working fine but i changed my data source and suddenly they are returning "blanks" and i can't figure out what went wrong!!

I have attached a sample data file

Column E - Create Time

Column M - First Response Time (Mins)

I want to create a measure that finds the average First Response Time (Mins) that occured during working hours (08:00-17:00) and those that occured outside working hours (17:01-7:59)

Thank you,

2 REPLIES 2
Super User

@anusornkhurana , Avg at what level

Example at hour level

Sumx( Values('Table'[Hour]),
CALCULATE(SUM('Table'[First Response (Minute)]),FILTER('Table', 'Table'[Create Time]<=TIME(17,00,00) && 'Table'[Create Time]>=TIME(8,00,00)))
)

Regular Visitor

At the hour level

average of first response (mins) that occured to all tasks created [Create Time] between 8.00-17.00

I tried the measure you gave me it didn't work. It is still returning Blank value 😞

Thank you!

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