Help with parameter

[Anonymous]

Hello,

I have a report calculating how many hours an employee works.  It has a formula that like:

SUM(Regular Hours) + SUM(Extra Hours) - Lunch Deduction.

I need to find out from the user what the lunch deduction is, so I"ve created a parameter with values .50, 1.0, 1.5, etc.  But now I don't know how to use this parameter on the report.  How do I display it, and how do I grab the value and add to my formula?

Thanks.

[calerof]

Hi @Anonymous ,

When you create a parameter you'll find it in the Fields pane at the right with a table of one column with a series of values depending on your choice when you created it, and a measure:

You just have to use that measure in your calculations.

Regards,

Fernando

[Anonymous]

Thank you Fernando, I must have not created the parameter properly because it is not in my field pane.  From the Power Query Editor Window, I clicked Manage Parameters, then New, and created Lunch Deduction Amount, then hit Close and Apply.  Is there something I am supposed to do after this?

[daxer-almighty]

You created the parameter in Power Query... It must be created in the app AFTER you've loaded data from PQ to the model. These two parameters serve different purposes.

[calerof]

In your case you need to use a What If Parameter, which is located under the Modeling ribbon:

Cheers,

Fernando

[Anonymous]

Thank you Fernando,

That worked but I can't get it to work in a formula, it just keeps using the alternateresult of 0.50, even though the slider says a different value:

WTF = 'QGenda Compliance'[Worked Hours]-'Lunch Deduction Amount'[Lunch Deduction Amount Value]

[calerof · Accepted solution]

@Anonymous 

With this code:

WTF = 
SUMX(WTF, [Working Hours Total] - 'Lunch Hours'[Lunch Hours Value])

See here:

Half hour lunchOne hour lunch

Works for me. Maybe you could share sample data and your pbix file.

F

[Anonymous]

I got it working.  What I did different...My Worked Hours was a Column, so I created it was a Measure (never know when to use column or measure), then I used the SUMX like you did and it worked. Thank you so much for your patience and help!

[calerof]

Good to hear. Rule of thumb: Always use measures, never use calculated columns. Unless it's completely necessary. 

😉

F

[Anonymous]

Thank you, I am going to go back and change my calculated columns into measures.

Maybe I should open a new thread on this, but can you use IF statements in Measures?  This is my formula in a column now, and trying to put in a measure, but it's not allowing me to pick that first field name (tagname):

Extra Pay Hours = IF('QGenda Compliance'[TagName] IN {"Shift: Specialty Shift", "Shift: Additional Shift", "Call: Specialty (Beeper)", "Call: Beeper", "PTO Buy-Back"}, 'QGenda Compliance'[Worked Hours], 0)

[daxer-almighty]

@Anonymous

You should not use calculated columns unless you don't have ANY OTHER CHOICE. However, turning calculated columns into measures is not always a good decision. Please note that a calculated column can be used in slicers, whereas measures can't. If you need a column, then the best solution by far is to calculate it in Power Query.