Daiy DAX : Day 346 FORMAT

Power BI DAX FORMAT Function

Description

The FORMAT function in DAX (Data Analysis Expressions) is used to convert a value into a specified text format. It is primarily used to display numbers, dates, or other data types as strings in a custom format for reporting purposes in Power BI.

Syntax

FORMAT(<value>, <format_string>)

• <value>: The value to be formatted (e.g., a number, date, or expression).
• <format_string>: A string specifying the desired format (e.g., "0.00" for numbers, "yyyy-mm-dd" for dates).

Return Value

A text string representing the formatted value.

Use Cases

• Custom Number Formatting: Display numbers with specific decimal places, percentages, or currency symbols.
• Date Formatting: Convert dates into user-friendly formats (e.g., "January 1, 2025" or "01/01/2025").
• Dynamic Labels: Create dynamic text for report visuals, such as titles or tooltips.
• Conditional Formatting: Combine with other DAX functions to create formatted text based on conditions.

Examples

1. Format a Number as Currency

   SalesFormatted = FORMAT(1234.567, "$#,##0.00")

   Result: "$1,234.57"

2. Format a Date

   DateFormatted = FORMAT(TODAY(), "mmmm d, yyyy")

   Result: "September 24, 2025" (based on current date)

3. Format a Percentage

   PercentFormatted = FORMAT(0.856, "0.00%")

   Result: "85.60%"

Common Format Strings

• Numbers:
  • 0: Digit placeholder (displays 0 if no digit).
  • #: Optional digit placeholder.
  • .00: Two decimal places.
  • $#,##0: Currency with thousands separator.
• Dates:
  • yyyy: Four-digit year.
  • mm: Two-digit month.
  • dd: Two-digit day.
  • mmmm: Full month name.

Notes

• The FORMAT function returns a text string, so it cannot be used directly in calculations.
• Use sparingly in large datasets, as it may impact performance.
• Ensure the format string matches the data type of the value (e.g., date formats for dates, number formats for numbers).

Related Functions

• VALUE: Converts a text string to a number.
• TEXT: Similar to FORMAT, but older and less flexible.