Is it possible to hold a column in a Measure variable?

[Anonymous]

We have a slicer with the value "Local" and "USD".

Depending on the selection we want to use a different column of data for calculations.

This works.

Billings Sum = 
IF(
    SELECTEDVALUE(CurrencyPickerTable[Currency]) = "Local",
    SUM('BillingsTable'[Billings (local)]),
    SUM('BillingsTable'[Billings (USD)])
)

However, it's going to get more complicated so I was hoping to hold the column in a variable to do calculations with later.

This is my failing attempt.

Billings Sum = 
var selectedBillingsColumn = 
IF(
    SELECTEDVALUE(CurrencyPickerTable[Currency]) = "Local", 
    SELECTCOLUMNS(BillingsTable, "local", [Billings (local)]), 
    SELECTCOLUMNS(BillingsTable, "USD", [Billings (USD)])
)

RETURN
SUM(selectedBillingsColumn)

How do you hold a column in a variable?

Thanks. 👍

[TomMartens]

Hey @Anonymous ,

there is no such thing as a dynamic table expression that can be used or re-used across different measures. For this there will be fractions of code that will repeat.

As the DAX function IF returns either a boolean value, or a scalar value, but never a table. A simple DAX statement to create a measure can become more complex.

Starting with this

the measure I want = 
CALCULATE(
    <the scalar value>
    , settablefilter1
    , settablefilter2
    , ...
    , settablefilterN
)

transforms into something like this

the measure I want = 
var _slicer1 = SELECTEDVALUE(...)
var _slicerN = SELECTEDVALUE(...)
return
CALCULATE(
    IF(_slicer voodod , the maybe hidden measure1 , the maybe hidden measure2)
    , settablefilter1
    , settablefilter2
    , ...
    , settablefilterN
)

I consider the measures "measure1" and "... measure2" base measures, these measures do not repsond to slicer selection, but simply calculate the value for local or USD. Be aware that "_slicer voodoo" represents logic that helps to determine on of the "maybe hidden base measures".

Basically we got the numeric expression part covered.

A final measure, reflecting on myriads of possible slicer combinations may look like this:

the measure I want = 
var _slicer1 = SELECTEDVALUE(...)
var _slicerN = SELECTEDVALUE(...)

var _check1 = IF( _slicermojo , 1 , 0)
var _check2 = IF( _slicermojo , 0 , 1)

return
CALCULATE(
    IF(_slicer voodod , the maybe hidden measure1 , the maybe hidden measure2)
    , AND( _check1 = 1 , FILTER('<Table1 1>' , ...)
    , AND( _check2 = 1 , FILTER('<Table1 2>' , ...)
    , ...
    , settablefilterN
)

You have to make sure that _check1 and _check2 can never have the same value at the same time.

As IF will be evaluated lazily, the second parameter of the AND above will never be evaluated if the the 1 parameter equals FALSE, this prevents the unwanted application of the FILTER(...) function.

Hopefully, this provides some ideas on how to tackle this challenge.

Regards,

Tom 

[Anonymous]

@Greg_DecklerThanks for the feedback. I updated the question with more focus on the first part of what I'm trying to solve. One thing at a time. 🤪

@TomMartensThanks for the response. I think you got the gist from the original question but please take a look at the updated version just in case.

[TomMartens]

Hey @Anonymous ,

you can store object in to a measure.

Next to that, you can't use SELECOLUMNS or any other funtion that return a tables inside IF. Both branches, true or false have to return a scalar value.

Regards,

Tom

[Anonymous]

Thanks @TomMartens.

Basically, the answer to my question is no, it can't be done.
And, the workaround is super complicated and not worth messing with. 🤪

I'm going to take another approach and reshape the imported data.

[Greg_Deckler]

@Anonymous I REALLY wish it was possible to return a table from a measure, it would be incredibly helpful! I think I have an idea posted about it.

[Greg_Deckler]

@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.