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 |