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). ...
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,

     

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?
  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    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,