Dynamic format strings for currency conversion in PowerBi with DAX

Dynamic format strings for currency conversion in PowerBi with DAX

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 dynamically format a measure by still using a slicer.

Model Prerequisite

Sample Model From AdventureWorks DW 2016

Add the FormatString column into your currency dimension

Currency Table with Currency Format String

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

Dynamic Format String Using Slicer

Here is the full dax script:
The _USDFormat variable retrieve 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.

Leave a Reply

Your email address will not be published. Required fields are marked *