I’ve previously written an article on how to dynamically convert and change the currency of a measure using a slicer.
Now let’s see how we can use dynamic format strings for currency conversion in PowerBi with DAX.
Add the FormatString column into your currency dimension
You can download the file with the sample format string from here.
Make sure to use a Unicode type column to store the different format strings otherwise the collation of your DB might not support all the characters.
Note that the format strings supported as an argument to the DAX FORMAT function are based on the format strings used by Visual Basic.
FORMAT( YourMeasure, SELECTEDVALUE( DimCurrency[FormatString], [optional] SelectedFormatString()-- or defaultCurrency )
Here the trick is to use the Format() function and the SelectedValue() function.
The SelectedValue() function returns the value of the column FormatString when the context has been filtered down to one distinct value only.
[Optional] If it has not been filtered down to only a single value it will return the alternate result which can be blank or in my case set to USD by default. You can also use the SelectedFormatString() function.
And here is our dynamic currency formatting slicer
Here is the full dax script:
The _USDFormat variable retrieves the default format String for USD.
When no currency is selected the USD conversion and format are applied by default.
For more explanation about the rest of the code, you can refer back to my previous post here.
Sales Amount Converted:= var _USDMeasure =sum(FactInternetSales[SalesAmount]) var _USDFormat = LOOKUPVALUE(DimCurrency[FormatString], [CurrencyName], "US Dollar") --retrieve default curency format return if(ISBLANK(_USDMeasure),blank(), if(HASONEFILTER(DimCurrency[CurrencyName]), -- the conversion calculation starts here -- the above part is just used to handle null values -- and slicer with multiple values allowed var _sumx =sumx( CROSSJOIN( values(DimCurrency[CurrencyKey]), values(DimDate[DateKey]) ), CALCULATE( var _USDMeasurex =sum(FactInternetSales[SalesAmount]) return Divide(_USDMeasurex,VALUES(FactCurrencyRate[AverageRate])) ) ) return FORMAT( _sumx, SELECTEDVALUE( DimCurrency[FormatString], _USDFormat ) ) ,FORMAT( _USDMeasure,_USDFormat) ) )
Note that as mentioned in my previous post the type of this calculated measure will be Text, hence, you won’t be able to perform other dependant calculations based on this measure.
Or, you’ll need to maintain extra measures in your model where you keep the reference measures with no dynamic formatting and apply the formatting on the dependant measures.