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

[Musker]

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

[123abc]

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.

[Musker]

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! 

[123abc]

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.

[Musker]

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 ) )