Passing Parameters in measures

[Anonymous]

Hi,

Thanks everyone for the comments for my previos posts.

I have one more question which I would like to clarify, Is there a way to parameterize values in Measures.

For eg: I have created a measure like below in which I would like to parametrize the Yearmonth value(highlighted), pls let me know even if we have any workarounds for this.

Total Sales = CALCULATE(
SUM('Headcount Data'[May-17]),
FILTER(Year_Month,Year_Month[Year_Month]=201705)
)

Regards,

[ara0530]

None of these are useful.  In all these solutions, you are 'hardcoding' the value.  How can we allow the user to change the parameter value in the report (i.e. like a slicer)?  

[Anonymous]

Hi,

Does anyone know is the opposite is possible? Can I store the value of a measure into a parameter?

[v-caliao-msft · Accepted solution]

@Anonymous,

Currently, we cannot use a parameter in a calculated measure directly. To work around this requirement, we could create a query to store parameter value in a dataset, and then use this dataset value in your calculated measure. Please refer to the sample steps below.

1. Sample parameter.
   
2. Create a blank query and edit the query in Advanced Editor
   
3. Create a measure in your original table like below.
   Total Sales = CALCULATE(
   SUM('Headcount Data'[May-17]),
   FILTER(Year_Month,Year_Month[Year_Month]=Max(Query2[ParameterDate]))
   )

Regards,

Chalrie Liao

[Anonymous]

I would like to learn more about this approch.

Do you have an videos or other material you could suggest?

[Phil_Seamark]

Hi @Anonymous

You could always use another measure for this. 

eg create a calculated measure like this.  You could even create a measure table called My Parameters to group them all together.

My Param = 201705

and then drop this into your other formula

Total Sales = CALCULATE(
SUM('Headcount Data'[May-17]),
FILTER(Year_Month,Year_Month[Year_Month]=[My Param])
)