cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SOTO
Regular Visitor

Count of events per hour

I have a table called INCIDENT where I want to be able to show how many INCIDENT_NUMBER are created each hour.

 

In the table there is a CREATION_DATE Colum in the format of DD/MM/YY HH:MM:SS

 

Any help please as I am new to DAX

1 ACCEPTED SOLUTION
mahoneypat
Super User
Super User

Here is a good way to do that

 

1. In the query editor, split your datetime column into date and time columns.  Select that column and click on the Split Column button, and choose Space as the delimiter.

2. Convert the columns to Date and Time type, respectively

3. Select the Time column and, on the Add Column tab, pull down on Time and choose Hour.  This will add a column with just the Hour.

4.  Make a Table or Matrix visual using the Hour field, and a simple count or distinct count of another field in that table

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@SOTO - There is an HOUR function in DAX. Also, you may find Open Tickets helpful. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Anonymous
Not applicable

Hi @SOTO 

Follow these steps

 

1. Change format of CreationDate column to  24 hour format [2001-03-14 13:30:55 (yyyy-mm-dd hh:nn:ss)].

2. Add a new calculated column SOH in your table(Incident) as below.

 

SOH = HOUR(Incident[CreationDate])

 

3. Add another calculated column in your table(Incident) as below.

 

No of Incidents/Hour =
VAR D = Incident[CreationDate].[Date]
VAR StartOfHour = Incident[SOH]
VAR Filtered =
    FILTER (
        ALL ( Incident ),
        Incident[CreationDate].[Date] = D
            && Incident[SOH] = StartOfHour
    )
RETURN
    CALCULATE ( DISTINCTCOUNT ( Incident[Incident Number] ), Filtered )

 

 

This should be enough.

 

Hope this helps.

 

Appreciate with a kudos.

Please mark as a solution if this resolves your problem.

 

Thanks

 

 

 

TomMartens
Super User
Super User

Hey @SOTO ,

 

make sure that your timestamp column is of type date/time, if it's not you can easily change the data type using PowerQuery like so:

TomMartens_0-1596889656456.png

You can also use Power Query to create a column that only contains the hour part of the timestamp like so:

TomMartens_1-1596889718030.png

Then leave Power Query using Close & Apply from the home menu.

 

Create a measure like so:

 

no of rows = COUNTROWS('Table') 

 

Adjust the table name 'Table' accordingly.

Change the Aggregation of the Hour column to don't summarize:

TomMartens_2-1596890053687.png

 

Create a new table visual, drag the hour column and the to the table - done:

TomMartens_4-1596890120195.png

 

hopefully, this gets you started.

regards

tom

 

 

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
mahoneypat
Super User
Super User

Here is a good way to do that

 

1. In the query editor, split your datetime column into date and time columns.  Select that column and click on the Split Column button, and choose Space as the delimiter.

2. Convert the columns to Date and Time type, respectively

3. Select the Time column and, on the Add Column tab, pull down on Time and choose Hour.  This will add a column with just the Hour.

4.  Make a Table or Matrix visual using the Hour field, and a simple count or distinct count of another field in that table

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.