The Format Function [DAX’s Functions!]
Contents
The DAX format function is a Power BI text function that allows you to convert a value to text in a format that you have specified.
It is a basic DAX function that most people learn on a DAX beginners course.
Syntax and Return Value of the Format Function
Syntax
The parameters of the DAX format function are as follows: –
| value: | A value or expression that evaluates to a single value. |
| format_string: | A string with the formatting to be used as a template. |
| In addition to setting this manually, Power BI contains some templates as well.
These templates are distinct from full Power BI templates. |
|
| [locale_name]: | This is optional. It is the name of the locale that will be used by the function. |
| If you do not use this then the locale used will be based on the Regional Settings in your Power BI. |
Return Value
A string containing the value which as been formatted as specified by the format_string.
PRO-TIP
The function returns an empty string if the value is BLANK.
If the format_string is left BLANK, the value will be formatted as “General Number” or “General Date” (based on the value data type) by default.
Simple Example of Format Function Being Used
Here’s a simple example of the DAX format function in use.
Let’s use Format to show 300.54 limited to one decimal place.
PRO-TIP
The Value argument can be a reference to a table column as well as a manually entered value (300.54).
Pre-Defined Numeric Formats
Power BI contains a set of built-in pre-defined numeric formats.
These are far simpler to use that the manual custom format string (“0.0”) we used above.
To use these you just type these pre-defined numeric formats in the format_string argument.
Format Strings
General displays the number without a number format.
Currency displays the number in a currency, the current is determined by your system settings.
Fixed shows at least one digit to the left and one digit to the right of the decimal point.
Standard shows at least one digit to the left of the decimal point and at least two digits to the right of the decimal separator, plus thousand separators.
Percent displays a number multiplied by 100, with the percent symbol just to the right of the number.
Scientific shows the number rounded to two decimal digits in scientific notation.
Let’s look at the example we used above and use a on of these predefined formats.
Custom Numeric Formats
Custom numeric formats allow you to take complete control of a number’s formatting.
Custom format expressions for numbers can have one to three format_string sections separated by semicolons.
If you use them, the first format_string will format positive numbers, the second format_string defines the format of negative numbers and the third format_string defines the format of a zero.
If you don’t use them then the format will be based on the first format_string.
The returned expression is then based on what the value argument contains.
These work very similarly to formating numbers in Excel.
Below is a combination of some cool custom numeric format tricks.
Using The Format Function Dynamically.
It’s worth mentioning that the DAX format function can be dynamically combined with other formulae and functions to create even more sophisticated and complex measures that are beyond the scope of this article.
Conclusion
The DAX format function is your best bet when you need to quickly format or show numbers in a different way.
Other DAX Articles
