Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Passing Parameters in measures

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,

Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    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

     

     

5 Replies

  • v-caliao-msft's avatar
    v-caliao-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      I would like to learn more about this approch.

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

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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])
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,

     

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

  • ara0530's avatar
    ara0530
    Icon for Advocate I rankAdvocate I
    3 years ago

    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)?