cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ARomain
Frequent Visitor

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
Super User I
Super User I

Re: Count cases by Hour

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


Did I answer your question? Mark my post as a solution!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

ARomain
Frequent Visitor

Re: Count cases by Hour

Test.PNG

Super User I
Super User I

Re: Count cases by Hour

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!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

ARomain
Frequent Visitor

Re: Count cases by Hour

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
Super User I
Super User I

Re: Count cases by Hour

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!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

ARomain
Frequent Visitor

Re: Count cases by Hour

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

Super User I
Super User I

Re: Count cases by Hour

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!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

ARomain
Frequent Visitor

Re: Count cases by Hour

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

Community Support
Community Support

Re: Count cases by Hour

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.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

Top Solution Authors
Top Kudoed Authors