My customer recently wanted to add international currency support to their Power BI reports. He gave me an excel file that had conversion rates for the current year and asked that the users be allowed to choose what currency format they wanted to see their data displayed in.
There is no easy ‘out of the box’ way to implement this. The slicer needs to slice the result of a measure which of course needs to be written generically. There was a great video on the Curbal YouTube channel that walked through a method of using slicers to change the format of currency values. I originally used this approach but ran into problems displaying the data in a matrix visualization. Since we were formatting the data as strings, the data would not sort properly in a matrix because the values were being string sorted instead of being treated as numbers.
Therefore, I went with the following approach that combined her very clever way of using slicers and helper tables with keeping the values as numbers so that we could sort the data properly.
I imported the Excel file as a stand alone table with no relations to any of the existing tables already in the model. It will exist as a simple lookup table.
Notice that since this is a US company, the raw data is stored in US Dollars so no conversion is needed for USD.
First step is to make a slicer based on the ‘Exchange Rates'[Currency Type].
Any measure that would give a result in currency has the following DAX at the beginning:
RTL SALES TY =
VAR cur =
HASONEVALUE ( ‘Exchange Rates'[Currency Type] ),
VALUES ( ‘Exchange Rates'[Currency Type] ),
VAR res = –Implement Measure Here
IF ( cur = “USD”, res, res * VALUES ( ‘Exchange Rates'[Exchange Rate] ) )
What happens is that when the measure is being calculated, we first check to see if the Exchange Rates table is being filtered down to one row. If so, we store the currency type in that row in the variable cur, otherwise we store the default value of “USD”. We implement our measure and store the value in a variable called res. The final line of the measure checks the value of cur and if we have selected a different currency type than “USD”, we multiply the result of the measure by the exchange rate.
The drawback of this approach is that we lost the ability to have a currency symbol associated with our measure results. Since we don’t know what currency the user is going to select, we can’t type the measure as a specific currency type. The customer wanted to ensure that users would be able to easily see what type of currency was being displayed so I created a simple measure that would show what currency type was selected.
Selected Currency =
SELECTEDVALUE ( ‘Exchange Rates'[Currency Type], “Multiple Selected” )
SELECTEDVALUE is very similar to HASONEVALUE. If a slicer is used in a visual to limit the ExchangeRates table to just one row, it will display the Currency Type of that row. If not, it would display ‘Multiple Selected’.
The following animated GIF is a quick demo of the approach with Worldwide Importers data.