Home » FORMAT Function DAX

FORMAT Function DAX

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..

LEN

UNICHAR

REPT

REPLACE

SUBSTITUE

CONCATENATEX

FIND

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 3,759 total views,  27 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.