Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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
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
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:
You can also use Power Query to create a column that only contains the hour part of the timestamp like so:
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:
Create a new table visual, drag the hour column and the to the table - done:
hopefully, this gets you started.
regards
tom
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |