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).
Book a Free Power BI Consultation and discover how to use DAX functions for smarter reporting.
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.% | .% |
Request a Demo of custom dashboards using FORMAT for financial, retail, and manufacturing analytics.
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.
Talk to a Power BI Expert at Addend Analytics to optimize your data visualization strategies.
FAQs on DAX FORMAT Function
Q1. What is the main purpose of the FORMAT function in DAX?
It converts values (numbers, dates, expressions) into text according to specified format strings for better readability and reporting in Power BI.
Q2. Can I use FORMAT in DirectQuery mode?
No. The FORMAT function does not work in DirectQuery when used with calculated columns or Row-Level Security (RLS).
Q3. How is FORMAT different from custom measures in DAX?
FORMAT is designed to change the presentation layer of data, whereas measures are used for calculations.
Q4. Does FORMAT support localization?
Yes. You can specify a locale (e.g., “en-US”, “fr-FR”) to present values in region-specific formats.
Q5. When should I avoid using FORMAT in Power BI?
Avoid using FORMAT for large datasets where performance is critical, since it returns text values and may slow down calculations.
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.
Ready to elevate your Power BI reports? Contact us today for a free consultation.
