DAX expression to display measure value based on dynamic parameter

[DaxUser100]

Hi

Can anyone help? I have a FACT table that stores various pre-calculated sales amounts expressed in 3 different currencies. So for example one row will have 3 separate columns which store the same amount but expressed in 3 different currencies SalesUSD, SalesEUR and one called SalesLOC (which is just the amount in the invoice currency). These columns however are hidden in the client tool because I want to use a different method of selecting them explained below.

There is a parameter table within the data model that sits by itself and is not related to the Fact table. In that parameter table are stored 3 currency codes USD, EUR and LOC.

The idea is that the parameter table will be placed in the report and the user selects only one value.

There will be a new measure created just called Sales which effectively should use a case statement along the following logical lines to determine which amount is displayed:

If user selects parameter cur code USD then display the value from the column SalesUSD in the table

If user selects parameter cur code EUR then display the value from the column SalesEUR in the table

Else just use SalesLOC  

Is it possible to create an expression to achieve this?  All my attempts so far have failed returning various unable to determine context errors

Any help would be appreciated

Br Alan

[danextian · Accepted solution]

Hi @DaxUser100,

Try this formula

Sales =
IF (
    HASONEVALUE ( 'CurrencyTable'[Currency] ),
    //returns a value only if a currency is selected
    SWITCH (
        SELECTEDVALUE ( 'CurrencyTable'[Currency], "" ),
        //currency being selected
        "USD", [SalesUSD],
        "EUR", [SalesEUR],
        [SalesLoc]
    )
)