Dynamic Currency Display in Filter

[ggmm17]

I have a currency filter where the user will select which currency to use to run the report however, 

If the scenario selected is 1, the only currency that will display is Euros

If the scenario selected is 2, it has to display Local currency based on the entity hierarchy description seelcted. 

The entity hierarchy table is linked to the flat entity table where the local currency is defined. 

The scenario table has a column called scenario currency and is either Euros is Scenario = 1 else, is blank. 

What i want to do is: if selected scenario currency = blank then look at selected entity and display the local currency

There is no relationship or common column between scenario and entity tables. 

What is the best approach? 

Thank you

[Anonymous]

Thanks for the reply from DataNinja777 , please allow me to provide another insight: 
Hi  @ggmm17 ,

I created some data:

Here are the steps you can follow：

1. Create a table of computed distinct entity columns with no joins as a slicer..

entity slicer table =
DISTINCT('entity tables'[entity])

2. Create measure.

Measure =
var _selectcurrency=SELECTEDVALUE('scenario table'[scenario currency])
var _selectentity=SELECTEDVALUE('entity slicer table'[entity])
RETURN
SWITCH(
    TRUE(),
    _selectcurrency=1 && MAX('entity tables'[currency])= "Euros",SUM('entity tables'[Value]),
    OR(_selectcurrency=2 , _selectcurrency= BLANK()) && MAX('entity tables'[entity])=_selectentity ,SUM('entity tables'[Value]))

3. Result:

Slicer – blank:

Slicer – 1:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[DataNinja777]

Hi @ggmm17 ,

You can simplify the measure by computing the relative exchange rate instead of looking up both separately. Since the exchange rate to EUR and the exchange rate to the local currency are reciprocals, there’s no need to look up both explicitly. Instead, we can derive one from the other.

Converted Amount =
VAR SelectedCurrency = SELECTEDVALUE(CurrencySelectionTable[Currency Option])
VAR TransactionCurrency = SELECTEDVALUE(FactTable[Currency Code])
VAR LocalCurrency = SELECTEDVALUE(EntityTable[Local Currency])

VAR ExchangeRateToEUR = 
    LOOKUPVALUE(CurrencyRates[Rate to EUR], CurrencyRates[Currency Code], TransactionCurrency)

VAR RelativeRate = 1 / LOOKUPVALUE(CurrencyRates[Rate to EUR], CurrencyRates[Currency Code], LocalCurrency)

RETURN
    SWITCH(
        TRUE(),
        SelectedCurrency = "EUR", SUM(FactTable[Transaction Amount]) / ExchangeRateToEUR,
        SelectedCurrency = "Local Currency", SUM(FactTable[Transaction Amount]) * RelativeRate
    )

This measure dynamically converts transaction amounts based on the selected currency in the slicer. If "EUR" is selected, it divides the transaction amount by the exchange rate to EUR. If "Local Currency" is selected, it multiplies the transaction amount by the reciprocal of the exchange rate to EUR, effectively converting it to the local currency.

Best regards,

[ggmm17]

Hi @DataNinja777  im not trying to calculate any amount, just dynamically display in the currency filter the currency type based on 1. scenario type and 2. entity