TL;DR
The DAX FORMAT function converts numbers, dates, and text into readable, presentation-ready strings using pre-defined options like Currency and Percent, or fully custom format strings. It’s essential for financial reporting, retail dashboards, and manufacturing KPIs where clarity matters, though it doesn’t work with DirectQuery in calculated columns or Row-Level Security, and can slow down performance on very large datasets. Use it thoughtfully, not on every measure.
Working with Power BI and DAX functions is all about transforming raw data into meaningful insights. One of the most versatile and widely used functions in DAX is the FORMAT function. This function is crucial when you need to display numbers, dates, and text values in a user-friendly format within your reports and dashboards.
For organizations implementing Power BI consulting solutions, the FORMAT function is frequently used in data visualization, financial reporting, and retail analytics dashboards where precision and clarity in data presentation are vital.
FORMAT
It Converts values to text based on the specified number format.
SYNTAX
FORMAT(<value>, <format_string> [, <locale_name>])
PARAMETERS
| S No | Term | Description |
| 1 | Value | It can be a number, value or an expression which evaluates to a single value. |
| 2 | Format String | A Format String is in which you want to format the value: There are pre-defined formatting options which you can use to format the values. You can also create your own format and convert the values based on the requirements. |
| 3 | Local Name | It is used to format value based on local name. It is an option argument. |
RETURN VALUE
- It returns single string value according to specified format in the formula.
- If you pass a blank value, it will return an empty string.
- If you specify the format string to blank, then it will convert the value in the default format (General Number or General Date) as per the data type of the value.
REMARKS
- Format string in the DAX FORMAT function only support argument which are based on the format strings of Visual Basic (OLE Automation), not on the format strings which are used by the .NET framework.
- FORMAT function does not work on Direct Query mode when used in the calculated column or with the Row-Level Security (RLS). .
PRE-DEFINED FORMAT STRINGS EXAMPLE
Below are some of the pre-defined format strings and its examples.
| S No. | Format Strings | Formula | Return Value |
| 1 | General Number | FORMAT(18572.38,”General Number”) | 18572.38 |
| 2 | Currency | FORMAT(18572.38,”Currency”) | $18572.38 |
| 3 | Fixed | FORMAT(18572.3892,”Fixed”) | 18572.39 |
| 4 | Standard | FORMAT(1862572.3892,”Standard”) | 1,862,572.39 |
| 5 | Percent | FORMAT(18.3881,”Percent”) | 1838.81% |
| 6 | Scientific | FORMAT(18.3881,”Scientific”) | 1.84E+01 |
| 7 | True/False | FORMAT(TRUE,”True/False”) FORMAT(1,”True/False”) | TRUE |
| 8 | On/Off | FORMAT(FALSE,”On/Off”) FORMAT(0,”On/Off”) | Off |
| 9 | Yes/No | FORMAT(True,”Yes/No”) FORMAT(1,”Yes/No”) | Yes |
IF the value is blank then it will return blank with any of the pre-defined format string.
USER-DEFINED/ CUSTOM FORMAT STRINGS
There are 3 types of USER-DEFINED/CUSTOM format strings for numbers. These sections are separated by semicolons. In case of pre-defined format strings, you can only use 1 section.
| S No. | Section | Description |
| 1 | One Section only | This will apply to all the values |
| 2 | Two Sections | In 2 sections, the 1st section applies to positive values and 0. The 2nd section applies to negative values. |
| 3 | Three sections | In 3 sections, 1st section applies to only positive values. The 2nd section applies to negative values And the 3rd section applies to 0. |
CUSTOM NUMERIC FORMAT CHARACTER
Below is the custom numeric format character which is used to format the values as per the choice.
| S No. | Character | Description |
| 1 | None | It Displays the number with no formatting |
| 2 | (0) | It is a digit placeholder which is used to display a digit or 0. if in the expression there is a digit in the position where format string has 0 then it will display the digit as it is otherwise it will display 0. |
| 3 | (#) | It is also a digit placeholder which is used to display the digit or nothing. if in the expression there is a digit in the position where format string has 0 then it will display the digit as it is otherwise it will display blank. |
| 4 | (.) | It is a decimal placeholder. It determines that how many digits should be displayed in the left or right of the decimal separator. If in the expression you only have single digit and you want to display it in the decimal format you can use 0 character after the decimal number. |
| 5 | % | It is a percentage placeholder. It multiplies the value by 100 and put the % sign as per you specify it in the format string. |
| 6 | , | It is a thousand separator. |
| 7 | : | It is a time separator. It separates the value in the Hour, Minute and Second format. |
| 8 | / | It is a date separator. It separates the value in Day, Month and Year format. |
| 9 | “MDH” | It displays the string or character which resides in the double quotation mark (“ “) |
EXAMPLE OF CUSTOM FORMAT STRINGS
| Expression | A = 55 | A = 0.55 | A = -55 | A = 0 |
| FORMAT(A,”0″) | 55 | 1 | -55 | 0 |
| FORMAT(A,”0.00″) | 55.00 | 0.55 | -55.00 | 0.00 |
| FORMAT(A,”#.##”) | 55. | .55 | -55. | . |
| FORMAT(A,”$#,#0;($#,#0)”) | $55 | $1 | ($55) | $0 |
| FORMAT(A,”$#,0.00;($#,0.00)”) | $55.00 | $0.55 | ($55.00) | $0.00 |
| FORMAT(A,”0.00%”) | 5500.00% | 55.00% | -5500% | 0.00% |
| FORMAT(A,”#.#%”) | 5500.% | 55.% | -5500.% | .% |
EXAMPLE OF DATE/TIME FORMAT
Below are some of the pre-defined DATE/TIME format strings. Other than that, there are custom format string also available to format value into DATE/TIME format.
| S No. | Format Strings | Expression | Return Value |
| 1 | General Date | FORMAT(DATE(2021,12,25) + TIME(18,15,10),”General Date”) | 12/25/2021 6:15:10 PM |
| 2 | Long Date | FORMAT(DATE(2021,12,25) + TIME(18,15,10),”Long Date”) | Saturday, December 25,2021 |
| 3 | Medium Date | FORMAT(DATE(2021,12,25) + TIME(18,15,10),”Medium Date”) | 25-Dec-21 |
| 4 | Short Date | FORMAT(DATE(2021,12,25) + TIME(18,15,10),”Short Date”) | 12/25/2021 |
| 5 | Long Time | FORMAT(DATE(2021,12,25) + TIME(18,15,10),”Long Time”) | 6:15:10 PM |
| 6 | Medium Time | FORMAT(DATE(2021,12,25) + TIME(18,15,10),”Medium Time”) | 06:15 PM |
| 7 | Short Time | FORMAT(DATE(2021,12,25) + TIME(18,15,10),”Short Time”) | 18:15 |
Practical Applications of FORMAT Function
· Retail Analytics: Presenting sales figures as currency across different geographies.
· Manufacturing Dashboards: Displaying production KPIs in thousands with separators.
· Finance: Formatting interest rates and percentages in investor reports.
· SSRS & Microsoft Fabric: Maintaining consistency of date/time fields across hybrid reporting solutions.
The DAX FORMAT function is a powerful tool for customizing the way data appears in Power BI dashboards and reports. By mastering pre-defined and custom formats, you can create professional, user-friendly reports that enhance decision-making.
At Addend Analytics, we help businesses streamline data with Power BI consulting, Microsoft Fabric integration, and custom DAX solutions. Whether you’re in retail, finance, or manufacturing, we can transform your reporting process into a competitive advantage.
Frequently Asked Questions
FORMAT function does not work in DirectQuery when used with calculated columns or Row-Level Security (RLS).
FORMAT is designed to change the presentation layer of data, whereas measures are used for calculations.
"en-US", "fr-FR") to present values in region-specific formats.
FORMAT for large datasets where performance is critical, since it returns text values and may slow down calculations.
FORMAT always converts the result to text, even if the original value was numeric. This means a formatted measure can’t be used in visuals that require a numeric value, like most charts, only in cards, tables, or matrix visuals that accept text.
FORMAT‘s text output would otherwise be rejected.
FORMAT‘s format strings follow Visual Basic (OLE Automation) syntax, not .NET string formatting. A common mistake is using "p" as shorthand for "Percent", which isn’t supported, the full word must be used instead.
Author By
Gaurav Lakhotia
Gaurav holds an MBA in Business Analytics. As a data geek and avid learner, he has earned accolades including Microsoft Certified Data Analyst, Microsoft Certified Azure Administrator, and active Power BI Community contributor. He is also a Microsoft Certified Trainer. With critical thinking and project delivery skills, he strives to achieve customer delight in Data Analytics projects. Gaurav loves travelling with friends when he is not busy working on data.
