FORMAT function in DAX

The FORMAT function in DAX (Data Analysis Expressions) is used to convert a value to text in a specific format. This is particularly useful for formatting dates, numbers, and other data types to display them in a more user-friendly way in Power BI reports.

Syntax

FORMAT(<value>, <format_string>)

• <value>: The value you want to format. It can be a column, measure, or constant.
• <format_string>: A string that specifies the desired format. Commonly used format strings are for dates, numbers, or custom formats.

Examples

1. Formatting Dates

You can use standard or custom date formats.

Example 1: Displaying Month and Year

FormattedDate = FORMAT(‘Sales'[OrderDate], “MMM YYYY”)

• If OrderDate is 2024-12-09, the result will be “Dec 2024”.

Example 2: Full Date with Day Name

Full Date = FORMAT(‘Sales'[OrderDate], “dddd, MMMM D, YYYY”)

• If OrderDate is 2024-12-09, the result will be “Monday, December 9, 2024”.

2. Formatting Numbers

You can format numbers as currency, percentages, or custom formats.

Example 1: Currency with Two Decimal Places

FormattedSales = FORMAT(‘Sales'[SalesAmount], “$#,##0.00”)

• If SalesAmount is 12345.678, the result will be $12,345.68.

Example 2: Percentage

FormattedPercentage = FORMAT(‘Sales'[DiscountRate], “0.00%”)

• If DiscountRate is 0.125, the result will be “12.50%”.

3. Combining Text and Formatted Values

You can use the FORMAT function with concatenation to create dynamic text.

Example: Dynamic Message

Message = “Total Sales: ” & FORMAT([TotalSales], “$#,##0.00”)

• If TotalSales is 50000.5, the result will be “Total Sales: $50,000.50”.

4. Custom Formats

You can use custom format strings to display values in unique ways.

Example: Leading Zeros

OrderNumber = FORMAT(‘Sales'[OrderID], “0000”)

• If OrderID is 45, the result will be “0045”.

5. Conditional Formatting with Text

The FORMAT function can be combined with conditional logic for dynamic displays.

Example: Highlight Values Above a Threshold

Status = IF([Sales] > 100000, “High: ” & FORMAT([Sales], “$#,##0”), “Low: ” & FORMAT([Sales], “$#,##0”))

• If Sales is 120000, the result will be “High: $120,000”.
• If Sales is 90000, the result will be “Low: $90,000”.

Common Format Strings

Note

• FORMAT returns a text value. You can’t perform numeric calculations on a value formatted with FORMAT.
• For performance optimization, avoid overusing FORMAT in large datasets where calculations are involved.