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,

     

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

Hi Anonymous ,

 

I create a pbix file with an example.

I hope this file helps you: Download PBIX 

 

 

Ricardo

  • 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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I understand but the probelm is i'm using direct query with 250millions records. writing more if conditions severely affects the performance. I'm looking for more elegant solution.