Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I have a fact table as below
Curreny Amount
usd 100
eur 75
cny 50
slicertable
Curreny rate_1 rate_2 rate_3
usd 1 1 1
eur 0.75 0.85 0.95
cny 0.50 0.65 0.80
rate_1_measure = 100*1 + 75*0.75 + 50*0.50
what I am trying is
Solved! Go to Solution.
Hi @Setosa
Please try below DAX in measure
Rate_1_Equivalent =
VAR CurrencyAmounts = SUMMARIZE('Facttable', facttable[Curreny], 'Facttable'[Amount])
VAR USD_Rate_1 = MAXX(FILTER('slicertable', slicertable[Curreny] = "usd"), 'slicertable'[rate_1])
VAR EUR_Rate_1 = MAXX(FILTER('slicertable', slicertable[Curreny] = "eur"), 'slicertable'[rate_1])
VAR CNY_Rate_1 = MAXX(FILTER('slicertable', slicertable[Curreny] = "cny"), 'slicertable'[rate_1])
RETURN
SUMX(
CurrencyAmounts,
SWITCH(
facttable[Curreny],
"usd", 'Facttable'[Amount] * USD_Rate_1,
"eur", 'Facttable'[Amount] * EUR_Rate_1,
"cny", 'Facttable'[Amount] * CNY_Rate_1
)
)
Let me know if that works for you
If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.
Thanks
Pijush
Linkedin
Proud to be a Super User! | |
Hi @Setosa
Please try below DAX in measure
Rate_1_Equivalent =
VAR CurrencyAmounts = SUMMARIZE('Facttable', facttable[Curreny], 'Facttable'[Amount])
VAR USD_Rate_1 = MAXX(FILTER('slicertable', slicertable[Curreny] = "usd"), 'slicertable'[rate_1])
VAR EUR_Rate_1 = MAXX(FILTER('slicertable', slicertable[Curreny] = "eur"), 'slicertable'[rate_1])
VAR CNY_Rate_1 = MAXX(FILTER('slicertable', slicertable[Curreny] = "cny"), 'slicertable'[rate_1])
RETURN
SUMX(
CurrencyAmounts,
SWITCH(
facttable[Curreny],
"usd", 'Facttable'[Amount] * USD_Rate_1,
"eur", 'Facttable'[Amount] * EUR_Rate_1,
"cny", 'Facttable'[Amount] * CNY_Rate_1
)
)
Let me know if that works for you
If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.
Thanks
Pijush
Linkedin
Proud to be a Super User! | |
thank you very much.
Hi @Setosa
If it is working, please mark my PREVIOUS comment as SOLUTION
Proud to be a Super User! | |