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.

Comments

Post a Comment

Popular posts from this blog

Daily DAX : Day 131 SELECTEDMEASURE

 The SELECTEDMEASURE function in Power BI's DAX (Data Analysis Expressions) language is a powerful tool used primarily within calculation groups to dynamically reference the measure currently being evaluated in a report. It’s part of the calculation group feature introduced to streamline complex reporting scenarios and improve maintainability by reducing the need to duplicate measure logic. What is SELECTEDMEASURE? SELECTEDMEASURE is a DAX function that returns a reference to the measure that is currently active or "selected" in the context of a calculation group. It doesn’t take any arguments and is typically used in the expression of a calculation item within a calculation group. This allows you to write generic logic that can dynamically apply to whichever measure is being evaluated in a visual, without hardcoding specific measure names. Syntax SELECTEDMEASURE()     Returns: A reference to the measure currently being evaluated in the report context. Use Case SELECTEDME...
Read more

Daily DAX : Day 446 INFO.CSDLMETADATA

Power BI DAX Function: INFO.CSDLMETADATA INFO.CSDLMETADATA` is a specialized DAX function introduced as part of a broader set of "INFO" functions (like `INFO.TABLES` or `INFO.MEASURES`) used for **model introspection**. It allows you to programmatically access the underlying structure of your Power BI semantic model. What does it do? The function returns a table with a single column containing the Conceptual Schema Definition Language (CSDL)  metadata of your model in XML format . In plain English, it exports a highly technical "blueprint" of your entire data model—including tables, columns, relationships, and measures—as a structured XML string. It is essentially the DAX equivalent of the `DISCOVER_CSDL_METADATA` Dynamic Management View (DMV) used in Analysis Services. --- Syntax and Output The syntax is straightforward as it requires no arguments: ```dax EVALUATE INFO.CSDLMETADATA() ``` Return Value : A table with one column and one row. Content : A long string ...
Read more

Daily DAX : Day 453 ENDOFWEEK

Power BI DAX Function: ENDOFWEEK Description The ENDOFWEEK function is a Time Intelligence function in DAX (Data Analysis Expressions) used in Power BI. It returns the date that represents the end of the week containing a specified date (typically Saturday, as weeks are considered to start on Sunday by default in DAX time intelligence). Syntax ENDOFWEEK( [, ]) : A column reference (usually from a Date table) or a date expression. Required. : Optional. Specifies the week-ending convention. Default is Saturday if omitted. Examples 1. Basic usage (default: week ends on Saturday): End of Week = ENDOFWEEK('Date'[Date]) For a date like January 8, 2026 (Thursday), this would return January 10, 2026 (Saturday). 2. In a measure for cumulative sales up to end of week: Total Sales to End of Week = CALCULATE( SUM(Sales[Amount]), DATESBETWEEN('Date'[Date], ST...
Read more