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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ARomain
Helper I
Helper I

Count cases by Hour

I have 2 columns, CaseID and Referral Date. The Referral Date is a normal time format (MM/DD/YYYY HH:MM:SS AM/PM) and I am trying to count the CaseID by the hour they were referred. 

 

How would I create a New Measure to do this? I can't seem to get the date to properly filter by hour.

 

 

11 REPLIES 11
v-jayw-msft
Community Support
Community Support

Hi @ARomain ,

 

There's no need to add new columns, please check following steps.

1# use Enter Data feature to create a table as below.

1.PNG

2# Create a measure as below.

count = COUNTROWS(FILTER(ALL('Table'),FORMAT('Table'[Time],"h")=FORMAT(SELECTEDVALUE('Table (2)'[hour]),"")))

Result would be shown as below.

2.PNG

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Thanks for help.

 

Unfortunately, I can't enter data either on my end. Everything is greyed out. 

 

I just find it odd that Power BI recoginizes the date field as a "date" but there is no way I can create a new measure formatting it only by hour. 

 

pic1.PNG

@ARomain ,

 

So you are using live connection mode to connect SSAS according to your screenshot? Otherwise the Enter Data feature won't be grayed out. If so I'm afraid you have to do the job in SSAS because many of the features of power bi are limited when using live connection.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
JarroVGIT
Resident Rockstar
Resident Rockstar

Can you give a sample of the data and your expected output? 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Test.PNG

Please please please don't post pictures of data, typing over datetime values is the absolute no hobby of ours 😂

Can you just copy paste from Excel into the textbox please? Thanks





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sorry about that. Does this work better?

 

CaseTime
A1231/1/2020 1:32
B1761/2/2020 1:32
C6751/3/2020 2:43
D4451/4/2020 3:23
E8761/5/2020 12:34

Hi @ARomain ,

That is no problem, thanks for sharing it like a copyable table 🙂 I loaded the data into Power BI as table Cases. THen I created a calculated table like this:

Table = 
ADDCOLUMNS(
    GENERATESERIES(1, 24), 
    "Count", 
    COUNTROWS(FILTER('Cases', HOUR('Cases'[Time]) = [Value])))

The GENERATESERIES creates a column from 1 through 24, then I create a second column "Count" and I count the rows where the Hour of Cases[Time] is the same as the current row of the table we are creating. The result is this:

image.png

Does this meet your requirements?

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I can't create a new column or table, as this data is coming from SQL server. I can only create a new measure.

 

I don't think this will work as a new measure. I tried it, but it doesn't work.

 

Thanks for your help though

Hi @ARomain ,

That seems like super important information, would've saved me a lot of time if you had put that in your opening post..

You can't create a measure that returns a 'list'  of numbers, and as far as I know you can't use the 'hour' in a date hierarchy (also, no hierarchies are created for you in directquery mode). So, without being able to create a list of numbers you also can't create a measure that is evaluated against every row, right? Is it possible to not use direct query?

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sorry about not clarifying. I asked for a new measure in the initial post, but I didn't specify it was from SQL. 

 

I'll talk with the SQL person at the company to see if she can throw in an extra column 

 

Sorry about any issues. Thank you again

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.