How to make this Dax shorter and better

[Anonymous]

I have a Dax that looks ok when I was working with sample test file, but after going to real dataset, I need a better solution.
I have a static currency table with exchange rate and currency code but no date and is connected to a fact table. Now user will select currency on slicer and will get converted amount of a measure in visual,
Switch( true(), [ selected currency]=“usd”, [measure]*values([exchange rate],..........

So same for all currencies listed, problem now I have a huge list in real data. Any suggestion on how to make it work.
Thanks much

[Anonymous]

hi @Anonymous 

If you already have you data in tables why would you use a switch as part of the calculation. 

You can use something like this, provided you tables are linked as you mentioned: 

Converted Amount = SUMX('Orders', RELATED(ExchangeRates[ExchangeRate]) * Orders[Price] * Orders[Qty])

Hope this helps,

Jan 
if this is a solution for you, don't forget to mark it as such. thanks

[Anonymous]

Thanks for your quick response.
I need to connect the calculation with slicer selection. That’s why I was trying that way. It worked with test sample. Got stuck with a static currency table where I have all countries listed.

[Pragati11]

Hi @Anonymous ,

Does your fact table has any columns that are in Currency table like Currency Code? If yes, then create a relationship between these 2 tables and create your calculation. In this case your slicer will work withiut using SWITCH function.

Thanks.

[Anonymous]

Thanks.
No I do not have any common filed in b/w fact table and currency table,
I ended up creating the following
Countrows(values(currency code))=1, sumx(table)[measure]/max[rate])), [ measure]
This is working without any relationship. And slicer created with code field.
So worked for me.
Thanks

[Pragati11]

Hi @Anonymous ,

Can you provide the structure of columns in both of your FACT table and Currency table? (The list of columns in both)

Thanks.

[Anonymous]

Although my problem got solved as I mentioned in my last message, still to answer your q
My fact table has many date fields and other columns with amount or text. My currency table has rate, currency code, country fields and it does not have date as this changes once in a year and I have now only rates for current year listed here.