cancel
Showing results for
Did you mean:
Regular Visitor

## Formula to do count of results in an hour timeframe per shift

Hey all - new to PowerBi so please barewith if this is such an easy thing.

I currently have data I'm trying to group up to tell me how many days there was 10 or more LowSeverity occurances in an hour during the day, and in what shift (Morning, Afternoon, Evening).

I've got it to work as a visual, however I need it as a calculation so I can add it to a card so it's easily visible on first look.

The Visual I have is filtering on 'AOI is Normal', 'Count of Low Severity is greater than or equal to 10', 'InitialSeverity is Low', 'Shift is Morning' (then the other visuals with similar filters for Afternoon and Evening).

This works to an extent as I can see what days this happened, and add up manually (example below). So here I can see I've had 22 Morning Shifts where there was an occurance of 10 or more in the hour.

My end goal is to have visual cards for Morning had x days, Afternoon had x days and Evening had x days.

Provided the below as sample of what I'm using field wise, but can provide more sample data if needed

 InitialSeverity RaiseTime RaiseTime - time AOI Shift Medium 10/07/2023 11:53:46 11:53:46 Normal Morning Low 10/07/2023 11:54:06 11:54:06 Normal Morning Low 10/07/2023 11:55:11 11:55:11 Normal Morning Low 10/07/2023 11:55:11 11:55:11 Normal Morning Low 10/07/2023 11:57:12 11:57:12 Normal Morning High 10/07/2023 12:18:10 12:18:10 Normal Morning Alert-Ack 10/07/2023 12:18:10 12:18:10 #FIELD NOT FOUND# Morning Alert-Ack 10/07/2023 12:18:10 12:18:10 #FIELD NOT FOUND# Morning Low 10/07/2023 12:18:45 12:18:45 Long Term Fault Morning Low 10/07/2023 12:24:05 12:24:05 Normal Morning Low 10/07/2023 12:25:32 12:25:32 Normal Morning High 10/07/2023 13:21:47 13:21:47 Normal Morning High 10/07/2023 13:24:47 13:24:47 Normal Morning Medium 10/07/2023 13:35:45 13:35:45 Normal Afternoon Low 10/07/2023 13:37:47 13:37:47 Normal Afternoon Low 10/07/2023 14:28:26 14:28:26 Normal Afternoon Medium 10/07/2023 14:31:25 14:31:25 Normal Afternoon Medium 10/07/2023 14:33:25 14:33:25 Normal Afternoon Medium 10/07/2023 14:36:27 14:36:27 Normal Afternoon

4 REPLIES 4
Post Prodigy

I see that you've made some adjustments to the DAX measures, and your approach seems reasonable. Let's review your DAX measures and make sure they are correct:

1. Hour of Day: This measure calculates the hour of the day based on the 'RaiseTime' column. It appears to be fine.

2. Low Severity Count: This measure calculates the count of LowSeverity occurrences for each date. It seems correct.

3. 10 or More Low Severity in Hour: This measure checks if the 'Low Severity Count' is greater than or equal to 10 and returns 1 or 0 accordingly. This also appears to be correct.

4. Shift Category: This measure categorizes the shifts based on the 'Shift' column. It looks good as well.

5. Morning Days: This measure calculates the distinct count of dates where the shift is "Morning," the hour is between 0 and 12, and there are 10 or more LowSeverity occurrences in the hour. However, I notice that you are using the 'Main Data' table for filtering, but it might be better to use the 'Hour of Day' and '10 or More Low Severity in Hour' measures directly in your calculation. Here's the updated measure:

Morning Days =

CALCULATE (

DISTINCTCOUNT ( 'Main Data'[RaiseTime].[Date] ),

FILTER (

'Main Data',

'Main Data'[Shift Category] = "Morning"

&& 'Main Data'[Hour of Day] >= 0

&& 'Main Data'[Hour of Day] < 12

&& 'Main Data'[10 or More Low Severity in Hour] = 1 ) )

Ensure that you've created a card visual for "Morning Days" and it should display the count of days with 10 or more LowSeverity occurrences in the morning shift.

If you're still encountering issues, please double-check the data and column names to make sure they match your actual dataset. Additionally, consider checking the data for any unexpected values or issues that might affect the results.

Regular Visitor

Unfortunately I just keep getting blanks response. Don't believe it to be the data as it's working fine as a visual on a table, but needing it for a card visual rather than manually counting the rows on the visual I currently have.

Back to the drawing board I go!

Post Prodigy

To calculate the count of days with 10 or more Low Severity occurrences in an hour during different shifts and display it on cards in Power BI, you can use DAX measures and calculated columns. Here's a step-by-step guide:

1. Create a calculated column that extracts the hour from the "RaiseTime" column:

Hour of Day = HOUR('YourTable'[RaiseTime])

Create a calculated column that calculates the total Low Severity occurrences for each day:

Low Severity Count = VAR CurrentDate = 'YourTable'[RaiseTime - time] RETURN CALCULATE( COUNTROWS('YourTable'), FILTER( 'YourTable', 'YourTable'[RaiseTime - time].[Date] = CurrentDate && 'YourTable'[InitialSeverity] = "Low" ) )

Create a calculated column that determines if there were 10 or more Low Severity occurrences in an hour:

10 or More Low Severity in Hour = IF('YourTable'[Low Severity Count] >= 10, 1, 0)

Create a calculated column that categorizes the shift based on your criteria:

Shift Category = SWITCH( TRUE(), 'YourTable'[Shift] = "Morning", "Morning", 'YourTable'[Shift] = "Afternoon", "Afternoon", 'YourTable'[Shift] = "Evening", "Evening", "Other" )

Create a measure to count the days with 10 or more Low Severity occurrences in the Morning shift

Morning Days = CALCULATE( DISTINCTCOUNT('YourTable'[RaiseTime - time].[Date]), FILTER( 'YourTable', 'YourTable'[Shift Category] = "Morning" && 'YourTable'[Hour of Day] >= 0 && 'YourTable'[Hour of Day] < 12 && 'YourTable'[10 or More Low Severity in Hour] = 1 ) )

Create similar measures for Afternoon and Evening shifts:

Afternoon Days = CALCULATE( DISTINCTCOUNT('YourTable'[RaiseTime - time].[Date]), FILTER( 'YourTable', 'YourTable'[Shift Category] = "Afternoon" && 'YourTable'[Hour of Day] >= 12 && 'YourTable'[Hour of Day] < 18 && 'YourTable'[10 or More Low Severity in Hour] = 1 ) ) Evening Days = CALCULATE( DISTINCTCOUNT('YourTable'[RaiseTime - time].[Date]), FILTER( 'YourTable', 'YourTable'[Shift Category] = "Evening" && 'YourTable'[Hour of Day] >= 18 && 'YourTable'[Hour of Day] < 24 && 'YourTable'[10 or More Low Severity in Hour] = 1 ) )

Now you have measures like "Morning Days," "Afternoon Days," and "Evening Days" that you can use in your Power BI cards to display the count of days with 10 or more Low Severity occurrences in each shift.

Regular Visitor

Thanks for the above, doesn't seem to work but not sure if it's me. I copied yours basically other than where it mentions 'YourTable'[RaiseTime - time].[Date] - as 'RaiseTime - time' is just a time rather than dates it didnt like it, so changed it to 'YourTable'[RaiseTime].[Date] instead.
Just seem to be getting Blanks back:

Below is what I have for each calculated/measure column you mentioned:

Hour of Day = HOUR('Main Data'[RaiseTime])

Low Severity Count = VAR CurrentDate = 'Main Data'[RaiseTime] RETURN CALCULATE( COUNTROWS('Main Data'), FILTER( 'Main Data', 'Main Data'[RaiseTime].[Date] = CurrentDate && 'Main Data'[InitialSeverity] = "Low" ) )

10 or More Low Severity in Hour = IF('Main Data'[Low Severity Count] >= 10, 1, 0)

Shift Category = SWITCH( TRUE(), 'Main Data'[Shift] = "Morning", "Morning", 'Main Data'[Shift] = "Afternoon", "Afternoon", 'Main Data'[Shift] = "Evening", "Evening", "Other" )

Morning Days = CALCULATE( DISTINCTCOUNT('Main Data'[RaiseTime].[Date]), FILTER( 'Main Data', 'Main Data'[Shift Category] = "Morning" && 'Main Data'[Hour of Day] >= 0 && 'Main Data'[Hour of Day] < 12 && 'Main Data'[10 or More Low Severity in Hour] = 1 ) )

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors