Forum Discussion

newpbiuser01's avatar
newpbiuser01
Helper V
3 years ago

Global Variable

Hello,

 

I am trying to create a global variable/parameter that I can use across multiple measures. One example would be setting a YearSelected variable  that can be used to calculate the total sales within that year. So if my measure was:

Sales = Calculate(Sum('Sales'[TotalSales]), filter('Sales', [Year] = "Y2022")),

this would get replaced with:

Calculate(Sum('Sales'[TotalSales]), filter('Sales', [Year] = YearSelected)) and the year would not need to be hardcoded in the measure. Is it possible to set a text parameter that can be used as a global variable across measures? 

 

Thank you for all your help!

Need Help

3 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    3 years ago

    Hi， newpbiuser01 

    Please enter a table with a column for your parameter values.

    Then you need to add a slicer to the report which is used to toggle the current value of the parameter and use the following measure to get the current value of the parameter.

     

    YearSelected = SELECTEDVALUE(Parameter[Year])
    Sales = Calculate(Sum('Sales'[TotalSales]), filter('Sales', [Year] = [YearSelected])),

     

    Best Regards,
    Community Support Team _ Eason

    • newpbiuser01's avatar
      newpbiuser01
      Helper V
      3 years ago

      Hi v-easonf-msft ,

       

      Do you know how I could use this to dynamically pick a column name? As an example, if I create a CustomYear table with just one value for the year, and then created a Year slicer and picked Y2022_Spend as the chosen value, would I be able to able to refer to the column dynamically-  instead of sum([Y2022_Spend]), refer to this Y2022_Spend column dynamically based on selectedvalue('CustomYear'[Year])? I hope that makes sense!

      CustomYear:

      Year
      Y2022_Spend

       

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        3 years ago

        Hi， newpbiuser01 

        I'm not sure what you mean by "instead of sum([Y2022_Spend]), refer to this Y2022_Spend column dynamically”.

        If ’Y2022_Spend‘,’Y2022_Spend‘ ,’Y2022_Spend‘ are column fields, you'd better to unpivot them in PowerQuery and then use the method I mentioned above.

        You can also consider creating measures to calculate sales for each year （Sales_Y2022，Sales_Y2021，Sales_Y2020) and then try ‘Field parameters’.

        Please let me know if I missed something.

         

        Best Regards,
        Community Support Team _ Eason