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 hope some one here can help me out, preferably in layman terms (i'm still new to the power bi).
I've been asked by my boss to set up a little view of our Spiceworks helpdesk system (ticket status, work time, customer, response time etc.) and it went great until he wanted an "average response time" but only during normal work hours.
Everything runs with a ODBC connection to an SQLite DB, Spiceworks automatically creates a column [created_at] as datetime of the creation time of the tickets(day/month/year HH:mm:ss). from here i've duplicated the column to [created_at_hour], extracted the HH:mm:ss and transformed it to time - so far so good.
now i would think that i need some kind of "if" statement, along the lines of
if [created_at_hour] > #time(8,0,0) and [created_at_hour] < #time(16,0,0) then [created_at_hour] else null
but it does not work, and i have no idea of why, but i do know that i properly mixed up M, statements and DAX - Sorry!
Wish full thinking:
I would like to end up with one column that only have time data, if [created_at_hour] is equals or bigger then 08:00:00 and 16:00:00 else "blank"
Solved! Go to Solution.
Hi @nyit,
It seems that you have made a mistake on the comma after '8'.
Please change the Comma in red to ';', this formula should work.
In addition, when you have written a dax formula, you could copy your dax formula to this dax tool which could help you check if your formula has syntax error and format it.
Hope this can help you!
Best Regards,
Cherry
Hi @nyit,
You could create the calculated column with the formula below.
Column =
IF (
AND (
HOUR ( 'table'[Created_at _hour] ) >= 8,
HOUR ( 'table'[Created_at _hour] ) < 16
),
'table'[Created_at _hour],
BLANK ()
)
Then you will get your expected output.
For reference, you could have a look at my test pbix file attached below.
Best Regards,
Cherry
Hi @v-piga-msft,
thank you for your reply
but apparently i'm not doing it right, so i don't know if it works.
perhaps you could help me out once more?
Hi @nyit,
It seems that you have made a mistake on the comma after '8'.
Please change the Comma in red to ';', this formula should work.
In addition, when you have written a dax formula, you could copy your dax formula to this dax tool which could help you check if your formula has syntax error and format it.
Hope this can help you!
Best Regards,
Cherry
Hi @v-piga-msft,
Sorry for the late answer.
Thank you, that worked and the dax formatter is really great to check for errors.
Hi @nyit,
That's great! My pleasure.
Best Regards,
Cherry
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |