Measures and Filters

[DivineNinja]

Currently, I have my data set to 3 shifts; A, B and C. I need to get a card to show the total number of days where there was more than 20 occurrences of High in an hour. I'm at a complete loss on how to do it, and tried a few measures but get 'Blanks' each time! 

Any help would be appreciated! Below is my data I'm working with:

Category	Time	Shift
High	22/07/2023 05:45:42	C
Medium	22/07/2023 05:56:39	C
Low	22/07/2023 05:59:35	C
Low	22/07/2023 06:00:00	C
Low	22/07/2023 06:00:07	C
Low	22/07/2023 06:04:45	C
Low	22/07/2023 06:20:31	C
High	22/07/2023 06:21:14	C
Low	22/07/2023 06:23:35	C
Low	22/07/2023 06:24:45	C
Low	22/07/2023 06:35:26	A
High	22/07/2023 06:44:17	A
Low	22/07/2023 06:55:35	A
High	22/07/2023 07:07:35	A
Low	22/07/2023 07:15:35	A
High	22/07/2023 07:21:57	A
High	22/07/2023 07:21:57	A
Low	22/07/2023 07:27:35	A
Low	22/07/2023 07:36:35	A
High	22/07/2023 07:39:35	A
Low	22/07/2023 07:39:35	A
High	22/07/2023 09:55:37	A
Low	22/07/2023 10:00:00	A
Low	22/07/2023 10:00:07	A
Low	22/07/2023 10:27:33	A
Low	22/07/2023 10:31:38	A
Low	22/07/2023 15:52:05	B
Low	22/07/2023 15:53:34	B
Low	22/07/2023 16:07:06	B
Low	22/07/2023 16:49:05	B
Low	22/07/2023 17:13:09	B
Low	22/07/2023 17:24:05	B
Low	22/07/2023 17:26:13	B
Low	22/07/2023 17:28:06	B
Low	22/07/2023 21:57:29	C
High	22/07/2023 21:57:29	C
Low	22/07/2023 22:00:07	C
Medium	22/07/2023 22:09:36	C
Low	22/07/2023 22:16:34	C
High	22/07/2023 22:20:00	C

[rsbin]

@DivineNinja ,

Please review the attached pbix file.

Solution involves creating several calculated columns and then finally a measure to Count Days > 20.

Hope you can follow through on the logic I used.  May need some tweaks depending on the remainder of your data set.

Good Luck and Regards,

[DivineNinja]

Thanks for the response. Strangely after copying your columns and changing to point at my table etc, it brings back '31' which seems to be day count, rather than times days where there has been 20 or more High Category

[rsbin]

@DivineNinja ,

Are the Columns calculating correctly?

Please show me the Measure you are using or better yet, can you attach your PBIX file?

[DivineNinja]

Doesnt seem to be giving me any errors, and everything is just copied from yours with the references changed to reference my Table. 
I can't share the PBIX file unfortunately as there's some sensitive info, however I've uploaded the data sample for the full month (Category & Time) - https://we.tl/t-7MSYp7mGCD 

Also below is the calculations I've used:

- Category_Count = CALCULATE( COUNTA( 'Main Data'[Category_Count ] ),

                       ALLEXCEPT( 'Main Data', 'Main Data'[Hours], 'Main Data'[Category_Count ] ))

- DateOnly = FORMAT( [Time], "mm/dd/yyyy" )

- Hours = Hour([Time])

- DaysCount_High > 20 = CALCULATE( DISTINCTCOUNT( [DateOnly] ),

                               FILTER( 'Main Data', 'Main Data'[Category] = "High" &&

                                       'Main Data'[Category_Count] > 20 )) + 0

[rsbin · Accepted solution]

@DivineNinja ,

Copied your data into my pbix as Sheet1. 
Then re-did all columns and the Measure.  Think I may have had to edit one of my calculated columns.

Result now comes out as 2.
Please review and hopefully this works for you.

Regards,

[DivineNinja]

Perfect that seems to be doing the trick, the numbers don't match up with what I calculated via Excel, so I'll check that out. But the calculations appear to be working.

Thank you!