Forum Discussion
Can a Measure = calculation use a Parameter Value?
I am trying to create a Status Board based on submitted Parameters.
The parameters I have created are:
Goal (Number)
Shift Start (Time)
Shift End (Time)
Break_Lunch (Number)
I am trying to create the following card values:
Clock = DATEDIFF(NOW(), Shift Start, Minute)
Avail Min = DATEDIFF(Shift End, Shift Start, Minute) - Break_Lunch
Target = Clock / Avail Min * Goal
When I click NEW MEASURE and enter this:
Measure = DATEDIFF(NOW(), Shift Start, Minute)
value 'Shift Start' is not recognized. Is it possible to use Parameters in a Measure object?
I found the solution.
Looks like Measure does not like spaces in object names.
I ended up creating 3 Measure objects to get the correct value:
Clock_Hr = FORMAT(NOW(), "hh") - FORMAT(Shift_Start, "hh")
Clock_Min = FORMAT(NOW(), "n") - FORMAT(Shift_Start, "n")
Clock = ([Clock_Hr] * 60) + [Clock_Min]
Hi MH-ofHC
The error that you are getting is because the function now returns a date /time and not a time:To get the desired result try :
Diff_now = DATEDIFF( TIME( HOUR( NOW() ), MINUTE( NOW() ), SECOND( NOW())),max('Table'[Start]),MINUTE)pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
4 Replies
- Ritaf1983
Super User
Hi MH-ofHC
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 I hope this clarifies,
Data from Manage Parameters that is included with a imported Oracle Database query:
Column Name Column Value Goal 30 Shift Start 5:30:00 AM Shift End 4:00:00 PM Break_Lunch 75 On my PowerBI report, I click the Oracle Database query and choose 'New Measure'
Top of the report it shows Measure =
I add the follow value: Measure = DATEDIFF(NOW(), Start Shift, Minute)
I get error: The syntax for 'Start' is incorrect. (DAX(DATEDIFF(NOW(), Shift Start, MINUTE))).
- Ritaf1983
Hi MH-ofHC
The error that you are getting is because the function now returns a date /time and not a time:To get the desired result try :
Diff_now = DATEDIFF( TIME( HOUR( NOW() ), MINUTE( NOW() ), SECOND( NOW())),max('Table'[Start]),MINUTE)pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
I found the solution.
Looks like Measure does not like spaces in object names.
I ended up creating 3 Measure objects to get the correct value:
Clock_Hr = FORMAT(NOW(), "hh") - FORMAT(Shift_Start, "hh")
Clock_Min = FORMAT(NOW(), "n") - FORMAT(Shift_Start, "n")
Clock = ([Clock_Hr] * 60) + [Clock_Min]