Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
ggmm17
Frequent Visitor

Dynamic Currency Display in Filter

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

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

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

I created some data:

vyangliumsft_0-1738654482046.png

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

vyangliumsft_1-1738654482048.png

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:

vyangliumsft_2-1738654516007.png

Slicer – 1:

vyangliumsft_3-1738654516008.png

 

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
Super User
Super User

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,

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.