Comprehensive Guide to the DAX FORMAT Function

Overview

The FORMAT function in DAX converts values (numbers, dates, times) to text according to specified formatting rules. This is essential for creating custom displays in Power BI reports, but it's important to understand both its capabilities and limitations.

Critical Consideration: FORMAT always returns text, which means the result cannot be used in visuals requiring numeric data types (like charts). For maintaining numeric data types while applying conditional formatting, use Dynamic Format Strings in Power BI instead.

Syntax

FORMAT ( <Value>, <Format> [, <LocaleName>] )

Parameters:

• Value (Required): A number, date, or expression that evaluates to a single value
• Format (Required): A format string defining how to display the value
• LocaleName (Optional): A locale identifier (e.g., "en-US", "de-DE", "pt-BR") for regional formatting differences

Returns: A single string value. Returns empty string if value is BLANK.

Working with Numbers

Predefined Numeric Formats

Example:

FORMAT( 12345.67, "Currency" ) // Returns: $12,345.67
FORMAT( 0.742, "Percent" ) // Returns: 74.20%

Custom Numeric Format Characters

Multi-Section Format Strings

Custom formats can have 1-3 sections separated by semicolons:

Examples:

Scaling Numbers (K, M, B, T)

Consecutive commas not followed by 0 before the decimal point divide the number by 1,000 for each comma:

VAR BigNumber = 103456789102
RETURN
    FORMAT( BigNumber, "$#,0" )         // $103,456,789,102 (Units)
    FORMAT( BigNumber, "$#,0,.0#K" )    // $103,456,789.1K (Thousands)
    FORMAT( BigNumber, "$#,0,,.0#M" )   // $103,456.79M (Millions)
    FORMAT( BigNumber, "$#,0,,,.0#B" )  // $103.46B (Billions)
    FORMAT( BigNumber, "$#,0,,,,.0#T" ) // $0.1T (Trillions)

Dynamic Scaling Based on Value

Working with Dates and Times

Predefined Date/Time Formats

Example:

VAR DT = DATE( 2021, 1, 2 ) + TIME( 15, 30, 0 )
RETURN
    FORMAT( DT, "Long Date" ) // Saturday, January 2, 2021

Custom Date Format Characters

Important Note: When "m" or "mm" immediately follows "h" or "hh", it displays minutes instead of months.

Custom Time Format Characters

Practical Date/Time Examples

The "c" Format - Adaptive Display

The "c" format automatically shows only relevant parts:

VAR D = DATE( 2020, 1, 2 )
VAR T = TIME( 15, 30, 0 )
VAR DT = D + T

FORMAT( DT, "c" ) // 1/2/2020 3:30:00 PM (both date and time)
FORMAT( D, "c" )  // 1/2/2020 (date only)
FORMAT( T, "c" )  // 3:30:00 PM (time only)

Duration Calculations (Longer than 24 Hours)

VAR StartEvent = DATE( 2020, 1, 2 ) + TIME( 15, 30, 0 )
VAR EndEvent = DATE( 2020, 1, 4 ) + TIME( 6, 18, 42 )
VAR DurationEvent = EndEvent - StartEvent

RETURN
    FORMAT( INT( DurationEvent ), "0:" ) & FORMAT( DurationEvent, "hh:nn:ss" )
    // Returns: 1:14:48:42 (1 day, 14 hours, 48 minutes, 42 seconds)

Localization with LocaleName Parameter

The optional third parameter allows you to specify regional formatting, which affects:

• Date/time component order and separators
• Month/day name translations
• Number decimal and thousand separators
• Currency symbols

Common Locale Identifiers

Localization Examples

Pro Tip: Use USERCULTURE() function result as the LocaleName parameter to automatically use the viewer's regional settings:

FORMAT( MyDate, "dd mmmm yyyy", USERCULTURE() )

Important Considerations and Best Practices

1. Data Type Conversion

• FORMAT always returns text/string data type
• Formatted measures cannot be used in chart visuals requiring numeric values
• Solution: Use Dynamic Format Strings for measures to maintain numeric type

2. BLANK Value Behavior

FORMAT( BLANK(), "Currency" ) // Returns: "" (empty string)
FORMAT( BLANK(), "0.00" )     // Returns: "" (empty string)

All formats return empty string when value is BLANK.

3. Format String Basis

• DAX FORMAT uses Visual Basic (OLE Automation) format strings
• NOT .NET Framework format strings
• Some .NET abbreviations (like "p" for Percent) won't work
• Use predefined strings or custom formats from this guide

4. DirectQuery Limitations

FORMAT is not supported in DirectQuery mode when used in:

• Calculated columns
• Row-level security (RLS) rules

5. Model Culture Settings

• Predefined formats use the model's culture property
• Power BI Desktop: Options > Regional Settings > Model language
• Analysis Services: Language property of the instance
• Default: User's computer locale

6. Performance Consideration

• Converting to text can impact performance in large datasets
• Consider if formatting is truly needed in the data model
• Often better to format in visualization layer

Practical Professional Scenarios

Scenario 1: Financial Reporting with Conditional Scaling

Scenario 2: Custom Date Display for Reports

Report Period =
VAR CurrentDate = MAX( Dates[Date] )
RETURN
    FORMAT( CurrentDate, "mmmm yyyy" ) // "November 2025"

Quarter Label =
VAR CurrentDate = MAX( Dates[Date] )
RETURN
    "FY" & FORMAT( CurrentDate, "yy" ) & " " & FORMAT( CurrentDate, "\QQ" )
    // "FY25 Q4"

Scenario 3: Multi-Language Support

Localized Month =
VAR CurrentDate = SELECTEDVALUE( Dates[Date] )
VAR UserLocale = USERCULTURE()
RETURN
    FORMAT( CurrentDate, "mmmm yyyy", UserLocale )

Scenario 4: Variance Display with Symbols

Variance Display =
VAR Variance = [Actual] - [Budget]
VAR FormatString =
    SWITCH(
        TRUE(),
        Variance > 0, "+$#,0;-$#,0;$0",
        Variance < 0, "$#,0;-$#,0;$0",
        "$0"
    )
RETURN
    FORMAT( Variance, FormatString )

Scenario 5: Duration Display for Project Management

Task Duration Display =
VAR StartDate = [Task Start]
VAR EndDate = [Task End]
VAR Duration = EndDate - StartDate
VAR Days = INT( Duration )
VAR TimeComponent = Duration - Days
RETURN
    IF(
        Days > 0,
        FORMAT( Days, "0" ) & " days, " & FORMAT( TimeComponent, "h:nn:ss" ),
        FORMAT( TimeComponent, "h:nn:ss" )
    )

Quick Reference Table

Most Common Format Patterns

Summary

The FORMAT function is powerful for creating custom text representations of values, but remember:

✅ Use FORMAT when:

• Creating display labels for tooltips
• Building custom text for card visuals
• Concatenating formatted values with text
• Exporting data with specific formatting

❌ Avoid FORMAT when:

• The measure will be used in charts/graphs
• You need to perform calculations on the result
• Performance is critical
• Working in DirectQuery mode with calculated columns/RLS

For maintaining numeric data types with conditional formatting in Power BI, explore Dynamic Format Strings as an alternative approach.