Using DAX to format a currency based on a format string stored in a dimensions

Question

I have a sales fact 'Sales' from multiple countries and I have a measure created in that fact table called 'Sales'[Revenue]

I'd like to create one measure that dynamically formats based on the the country, so US sales would be formatted $100 and UK sales as £100 (if the filter context included multiple countries then it could just omit the currency symbol or return blank())

I have a country dimension where I have add the format string as an attribute.

How could this be achieved in DAX? The only option I know will work is to create 5 separate measures [Revenue $], [Revenue £] etc. but that will create clutter in the client tools. I've looked at using FORMAT but can't quite get it to work

Thanks

Answer 1

I got around a similar problem by using a lookup table. I'll apply my solution to your case.

create a table called currencycodes with the columns (symbol,currencycode,id). It will store the every currency code along with its symbol.

Make sure your sales table stores the currencycodeid which indicates the currency the sales was made in.

Use the following algorithm to generate a unique id per currency code

(((int) CurrencyCode[0]) * 10000)

(((int) CurrencyCode[1]) * 100)

(((int) CurrencyCode[2]))

E.x.: USD = 85|83|68 = 850000 + 8300 + 86 = 858386

we're converting the character to the ASCII code and making it a number. This allows us to use MIN/MAX to get the distinct currency code in DAX as a scalar.

Inside your measure, you do the following

IF

(

DISTINCTCOUNT('Sales'[CurrencyCodeId]) = 1,

LOOKUP('CurrencyCodes'[Symbol], 'CurrencyCodes'[Id], MIN('Sales'[CurrencyCodeId])) & " " & SUM('Sales'[Amount]),

BLANK()

)

So in the measure, we get the # of unique currency code ids. If they're all the same, we use the lookup function to get the symbol and concatenate it with sum of the sales amount.

If the MIN/MAX wouldn't be necessary if there were some way to retrieve the first value as a scalar. TOPN returns rows.