Forum Discussion

Anonymous's avatar
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 ...
Need Help
JarroVGIT's avatar
JarroVGIT
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's avatar
Anonymous
Not applicable
6 years ago

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
  • JarroVGIT's avatar
    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's avatar
      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

      • JarroVGIT's avatar
        JarroVGIT
        Resident Rockstar
        6 years ago

        Hi Anonymous ,

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