Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DivineNinja
New Member

Measures and Filters

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:

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

 

1 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,

View solution in original post

6 REPLIES 6
rsbin
Super User
Super User

@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,

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

@DivineNinja ,

Are the Columns calculating correctly?

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

 

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



@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,

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!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors