Forum Discussion

Anonymous

Not applicable

6 years ago

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

Anonymous

Not applicable

6 years ago

Resident Rockstar

6 years ago

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

Anonymous
Not applicable
6 years ago
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
  Resident Rockstar
  6 years ago
  Hi Anonymous ,
  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! 🙂
  - Anonymous
    Not applicable
    6 years ago
    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