Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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
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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
3 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
10 | |
4 | |
3 | |
2 | |
2 |