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

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.

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

    Anonymous ,

     

    I don't see a problem using if, once you are gonna filter it and measures work only on the fly.

    Does your database have 1 column per country or are you pivoting on your query ?

     

    Ricardo