Dynamic field names in mesures

[Anonymous]

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.

[Ashish_Mathur]

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]

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]

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 · Accepted solution]

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]

Hi @Anonymous ,

I create a pbix file with an example.

I hope this file helps you: Download PBIX 

Ricardo

[Anonymous]

@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]

@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]

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]

@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