DAX – Format Function

  • Post category:Power BI
  • Post author:
  • Post published:October 6, 2021

FORMAT 
It Converts values to text based on the specified number format. 

SYNTAX

FORMAT(<value>, <format_string> [, <locale_name>]) 

PARAMETERS 

S No Term Description 
Value It can be a number, value or an expression which evaluates to a single value. 
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. 
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 
General Number FORMAT(18572.38,”General Number”) 18572.38 
Currency FORMAT(18572.38,”Currency”) $18572.38 
Fixed FORMAT(18572.3892,”Fixed”) 18572.39 
Standard FORMAT(1862572.3892,”Standard”) 1,862,572.39 
Percent FORMAT(18.3881,”Percent”) 1838.81% 
Scientific FORMAT(18.3881,”Scientific”) 1.84E+01 
True/False FORMAT(TRUE,”True/False”) FORMAT(1,”True/False”) TRUE 
On/Off FORMAT(FALSE,”On/Off”) FORMAT(0,”On/Off”) Off 
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 
One Section only This will apply to all the values 
Two Sections In 2 sections, the 1st section applies to positive values and 0. The 2nd section applies to negative values. 
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 
None It Displays the number with no formatting 
(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.  
(#) 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. 
(.) 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. 
It is a percentage placeholder. It multiplies the value by 100 and put the % sign as per you specify it in the format string. 
It is a thousand separator. 
It is a time separator. It separates the value in the Hour, Minute and Second format. 
It is a date separator. It separates the value in Day, Month and Year format. 
“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 -55 
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 
General Date FORMAT(DATE(2021,12,25) + TIME(18,15,10),”General Date”) 12/25/2021 6:15:10 PM 
Long Date FORMAT(DATE(2021,12,25) + TIME(18,15,10),”Long Date”) Saturday, December 25,2021 
Medium Date FORMAT(DATE(2021,12,25) + TIME(18,15,10),”Medium Date”) 25-Dec-21 
Short Date FORMAT(DATE(2021,12,25) + TIME(18,15,10),”Short Date”) 12/25/2021 
Long Time FORMAT(DATE(2021,12,25) + TIME(18,15,10),”Long Time”) 6:15:10 PM 
Medium Time FORMAT(DATE(2021,12,25) + TIME(18,15,10),”Medium Time”) 06:15 PM 
Short Time FORMAT(DATE(2021,12,25) + TIME(18,15,10),”Short Time”) 18:15  

Addend Analytics is a Microsoft Gold Partner based in Mumbai, India, and a branch office in the U.S. Addend has successfully implemented 100+ Microsoft Power BI and Business Central projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia. Get a free consultation now by emailing us at kamal.sharma@addendanalytics.com or Contact us.