Count cases by Hour

[ARomain]

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.

[v-jayw-msft]

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.

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.

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.

[ARomain]

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. 

[v-jayw-msft]

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

[JarroVGIT]

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

[ARomain]

[JarroVGIT]

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

[ARomain]

Sorry about that. Does this work better?

Case	Time
A123	1/1/2020 1:32
B176	1/2/2020 1:32
C675	1/3/2020 2:43
D445	1/4/2020 3:23
E876	1/5/2020 12:34

[JarroVGIT]

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:

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! 🙂

[ARomain]

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

[JarroVGIT]

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! 🙂

[ARomain]

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