If you’ve built more than a handful of Power BI semantic models, you know the fact that the requirements of numeric format strings are different in different scenarios. One needs currency symbol prefix ($, or ₹ etc). One needs thousand separator, one needs values to be in multiples of thousand like (K, M, B, T) and one needs the negative numbers to be in brackets and one needs an arrow or a delta symbol after the value. Likewise the requirements are a ton. In such scenarios often developers rely on FORMAT function and change the formatting which could convert the number measure into text and degrades the performance as well. Or they rely on Dynamic Format String which is lot better than FORMAT function, however, hardcoding the format strings in each and every measure can also cause problems, what if your customer asks you to change the currency symbol from dollar to Euro, you need to update each and every format string manually or use TMDL view to update them in bulk.
What if I tell you that I wrote one model independent DAX User Defined Function which can cover all the scenarios of numeric value formatting and you can use it in every model? Yes you read it right, lets understand how it works.
I am taking a numeric column with values ranging from negative 1 trillion to positive 3 trillion to demonstrate this.
I created a DAX measure called NumericValue and set its format string to dynamic.
NumericValue = SUM(NumbericTable[Number])
Now I will show you how the function “GetNumericDynamicFormatString” applies different format strings to this measure just by passing different combination of argument values — without writing any additional code.
The function accepts 10 arguments:
1. Val (ANYREF) — The numeric value passed to the function.
2. numberOfDecimalPlaces (NUMERIC) — Number of decimal places you want to show.
3. currencyName (STRING) — Name of the currency you want to prefix. Supported values are “United States Dollar”, “Indian Rupee”, “Europe Euro”, “Chinese Yuan” and “Japanese Yen”.
4. thousandSeperator (BOOLEAN) — Set to TRUE() to add comma separators.
5. multiplesOfThousand (BOOLEAN) — Set to TRUE() to auto-scale the number and display it as K, M, B or T depending on its value.
6. negativeNumbersBracket (BOOLEAN) — Set to TRUE() to display negative numbers as (value) instead of -value.
7. truncateZero (BOOLEAN) — Set to TRUE() to display zero as plain 0, ignoring all other formatting.
8. deltaIconRequired (BOOLEAN) — Set to TRUE() to append a trend icon after the value.
9. preferredPositiveDeltaIcon (STRING)_ — The icon to show after positive values. Works only when deltaIconRequired is TRUE(). You can pass any symbol like ↑, 📈 or ✓.
10. preferredNegativeDeltaIcon (STRING) — The icon to show after negative values. Works only when deltaIconRequired is TRUE(). You can pass any symbol like ↓, 📉 or ✗.
Scenario 1 Increase the number of decimal places to 4. Set **numberOfDecimalPlaces** to 4, leave everything else off.
getNumericDynamicFormatString(SELECTEDMEASURE(), 4, "", FALSE(), FALSE(), FALSE(), FALSE(), FALSE(), "","")
Scenario 2: Indian Rupee currency symbol
Pass “Indian Rupee” as currencyName and the ₹ symbol gets prefixed automatically.
getNumericDynamicFormatString(SELECTEDMEASURE(), 4, "Indian Rupee", FALSE(), FALSE(), FALSE(), FALSE(), FALSE(), "","")
Scenario 3: Thousand Separator
Set thousandSeperator to TRUE() and commas appear in the right places.
getNumericDynamicFormatString(SELECTEDMEASURE(), 4, "Indian Rupee", TRUE(), FALSE(), FALSE(), FALSE(), FALSE(), "","")
Scenario 4: Auto scale to K, M, B, T Enable multiplesOfThousand and the function checks the absolute value at runtime and decides whether to show K, M, B or T. No separate measures, no IF conditions in your format string.
getNumericDynamicFormatString(SELECTEDMEASURE(), 4, "Indian Rupee", TRUE(), TRUE(), FALSE(), FALSE(), FALSE(), "","")
Scenario 5: Bracket style negatives Set negativeNumbersBracket to TRUE() and your negatives render as (1,234) instead of -1,234. Finance teams will stop complaining.
getNumericDynamicFormatString(SELECTEDMEASURE(), 4, "Indian Rupee", TRUE(), TRUE(), TRUE(), FALSE(), FALSE(), "","")
Scenario 6: Clean zero display. Enable truncateZero and zeros show as plain 0 without any decimal clutter around it.
getNumericDynamicFormatString(SELECTEDMEASURE(), 4, "Indian Rupee", TRUE(), TRUE(), TRUE(), TRUE(), FALSE(), "","")
Scenario 7: Delta icons Set deltaIconRequired to TRUE() and pass your preferred symbols. Positives get ↑, negatives get ↓ — embedded directly in the format string. Your measure stays numeric throughout, no text conversion.
getNumericDynamicFormatString(SELECTEDMEASURE(), 4, "Indian Rupee", TRUE(), TRUE(), TRUE(), TRUE(), TRUE(), "↑","↓")
Likewise you can mix and match these arguments to get any combination you need from a single function call. Please note, at present it is not possible to set user defined function arguments as optional, which means you need to enter appropriate values for each argument in order to use them. Otherwise it throws an error.
Since LinkedIn and Medium don’t have great options for displaying DAX/TMDL code properly, I have made the full TMDL code available on my website: click here. Just copy it, paste it into your Power BI file and run it once and make sure the function got created.
Then you can start using that function to format your numeric DAX measures or columns. No additional configuration needed.
I have also made this FormatString function available in the SQL BI DAX library platform along with all my other DAX functions: Font and TimeConversion. All functions are platform independent so you can use them in any semantic model without any issues. Hope you find this useful, would love to hear your thoughts below.
Happy Learning!
Super User