Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic field names in mesures

Let’s say I’ve below areas as a table value.

Area

USA

UK

AUS

CAN

 

I’ve created a measure for selected item from the Area table. (It’s always one selected in slicer).

SelectedArea = SelectedValue(Area)

Now, suppose my fact table has different sales columns for each area like below.

Sales

Sales_USA

Sales_UK

Sales_AUS

Sales_CAN

 

USA Sales := Sum(Sales_USA)

So, my question is here instead of creating Sales for each area, can we automate this something like below?
Sales := Sum(Sales_[SelectedArea])

Based on the selection in Area column the measure should update.

I can create measure of each area and use switch statement but the problem is I’ve more than 50 different measures.

Any help would be appreciated.

Need Help
Tips and Tricks
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    Sorry for our delay in response, but it does not support to calculate dynamic formula/field in measure, but we can use "unpivot" in power query editor to meet your requirement:

     

     

     

    If you do not want to change the construction of data, we can use switch to calculate for each area:

     

     

    Sales =
SUMX (
    DISTINCT ( Country[Country] ),
    SWITCH (
        [Country],
        "USA", SUM ( Sales[Sales_USA] ),
        "BRA", SUM ( Sales[Sales_BRA] ),
        "CAN", SUM ( Sales[Sales_CAN] ),
        "JPN", SUM ( Sales[Sales_JPN] )
    )
)

     


    By the way, PBIX file as attached.


    Best regards,

     

9 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      camargos88 Thanks for your quick response. I know this is one way of doing it. But I wanted to completly avoid creating bunch of measures or If conditions. Essentially I want to create a single measure and dynamically update the column name based on the selections in area slicer.

       

      SelectedValue = SelectedValue(area)

      Current: Sum(Sales_USA) (for each area)

      Expected: Sales := Sum(Sales_[SelectedValue])

       

      Suppose If I selected USA, my sales measure should become Sum(Sales_USA) and If i select UK it should become Sum(Sales_UK). Simply I want to control [SelectedValue] based on selection in area slicer. So this way I can avoid creating so many measures or if conditions. 
      Is it possible to use varible or measure in the column names? 

      • camargos88's avatar
        camargos88
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Anonymous ,

         

        I don't know how to do it without if.

        I sent you an example that you can apply for n number of columns in 1 measure. You don't need to create a lot of measures.

         

        I hope it helps,

         

        Ricardo

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    Your data is not well arranged.  You should unpivot your data to have all Countries appear in 1 column only and in another column you have have the sales figures.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Ashish_Mathur , I took the the above data just an example to explain the scenario but in reality the data and the usecase is different and we can't change the data foramt for various reasons. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I've created a measure as below.

        Sales =
        var SelectedCountry = SELECTEDVALUE(Country[Country])
        var Exp = "Sum(Sales[Sales_"&SelectedCountry&"])" // this is returning Sum(Sales_USA)
        return CALCULATE(Exp) //here the return statement not evaluating the Exp variable instead it's just dislaying Sum(Sales_USA)
         
        Is there anyway to evaluate the variable inside calculate?