FORMAT function is a Power BI text function in DAX, which converts a value to text according to the specified format.
Syntax
FORMAT(<value>, <format_string>)
| value | is a value or expression that evaluates to a single value. |
| format_string | is a string with the formatting template. |
The return type, a string containing value formatted as defined by format_string.
There are some predefined formats such as numeric formats, and date/times formats that can be specified in the format_string argument, you can also create a custom formats.
Lets take an example of using FORMAT function in Power BI.
Predefined numeric formats
Following are some predefined numeric formats that can be specified in the format_string argument to converts a value to text according to the specified numeric format.
“General Number” : It displays number with no thousand separators, or you can say with no formatting.
Lets create a new column, add a New Column tab in ribbon bar, and write following DAX, which converts the number into general numbers as shown below.
General Number = FORMAT(NumericFormats[Number], "General Number")
“Currency” : It displays number with your currency locale formatting.
Currency = FORMAT(NumericFormats[Number], "Currency")
“Fixed” : It displays at least one digit to the left and two digits to the right of the decimal separator.
Fixed = FORMAT(NumericFormats[Number], "Fixed")
“Standard” : It displays number with commas as thousand separators and at least one digit to the left and two digits to the right of the decimal separator.
Standard = FORMAT(NumericFormats[Number], "Standard")
“Percent” : It displays number multiplied by 100 with a percentage sign %. It always displays two digits to the right of the decimal separator.
Percent = FORMAT(NumericFormats[Number], "Percent")
“Scientific”: It displays the number in scientific notation with two decimal digits.
Scientific = FORMAT(NumericFormats[Number], "Scientific")
“Yes/No”: It display “No” if number is 0 else display “Yes”.
Yes/No = FORMAT(Numbers[Number], "Yes/No")
“True/Flase” : It displays “False” if number is 0 else displays “True”.
Ture/False = FORMAT(Numbers[Number], "True/False")
“On/Off” : It displays Off if number is 0 else display On.
On/Off = FORMAT(Numbers[Number], "On/Off")
Predefined Date/Time formats
Following are some predefined Date/Time formats that can be specified in the format_string argument to converts a value to text according to the specified Date/Time format.
“General Date” : It displays a date, or date and time. Date is evaluated by your system settings.
Short Date” : It displays a date according to your current culture’s short date format. For example, 01/31/2021.
Short Date = FORMAT(Dates[Date], "Short Date")
“Long Date” : It displays a date according to your current culture’s long date format. For example, Sunday, January 31, 2021.
Long Date = FORMAT(Dates[Date], "Long Date")
“Medium Date” : It displays a date according to your current culture’s medium date format. For example, 31-Jan-21.
Medium Date = FORMAT(Dates[Date], "Medium Date")
“Long Time” : It displays a time using your current culture’s long time format, generally includes hours, minutes, seconds. For example, 10:19:42 AM.
Long Time = Format(Date_Time[Date/Time], "Long Time")
“Medium Time” : It displays a time in 12 hour format. For example, 10:19 AM.
Medium Time = Format(Date_Time[Date/Time], "Medium Time")
“Short Time : It displays a time in 24 hour format. For example, 10:19.
Short Time = Format(Date_Time[Date/Time], "Short Time")
Also Read..
