Data labels in Power BI play a crucial role in enhancing the readability and usability of reports. Well-formatted labels help users interpret data more efficiently, reducing cognitive load and improving decision-making. One of the most powerful ways to improve data labels is by using format strings in Power BI.

This article will explore how to use format strings effectively in Power BI, including custom formatting techniques, examples, and best practices.

Understanding Format Strings in Power BI

Format strings in Power BI allow you to control the appearance of numbers, dates, and text in visuals without modifying the underlying data. These format settings are applied to measures and columns to enhance clarity and consistency.

Power BI supports two main types of format strings:

Standard format strings – Predefined formats like currency, percentage, and date formats.
Custom format strings – User-defined formats that provide more control over how data labels appear.

Applying Format Strings in Power BI

There are multiple ways to apply format strings in Power BI, including:

Using the Power BI Desktop UI
Applying Format Strings in DAX Measures
Defining Format Strings in the Tabular Model

1. Applying Format Strings in Power BI Desktop

In Power BI Desktop, you can format data labels directly through the user interface:

Select a column or measure in the Fields pane.
In the Column Tools or Measure Tools tab, locate the Format dropdown.
Choose an appropriate format, such as Currency, Percentage, or Date.
Power BI will automatically apply the format in all visuals using that field.

While this method is simple, it may not provide the flexibility needed for complex formatting scenarios.

2. Custom Format Strings Using DAX

DAX (Data Analysis Expressions) provides powerful functions to create custom format strings for measures. The FORMAT function is particularly useful:

Syntax:

DAXCopyEditFORMAT(<value>, <format_string>)

Examples of Custom Format Strings

Formatting Numbers

Convert a numeric value into a currency format:
```
  DAXCopyEditRevenueFormatted = FORMAT([Revenue], "$#,##0.00")
```
- Example Output: $12,345.67
Display large numbers in a compact format:
```
  DAXCopyEditSalesCompact = FORMAT([Sales], "#,##0,, M")
```
- Example Output: 3.5 M (for 3,500,000)

Formatting Percentages

Display values as percentages with two decimal places:
```
  DAXCopyEditProfitMargin = FORMAT([Profit] / [Revenue], "0.00%")
```
- Example Output: 12.50%

Formatting Dates

Display a date in a custom format:

  DAXCopyEditCustomDate = FORMAT([OrderDate], "MMMM dd, yyyy")

Example Output: March 25, 2025

Show only the month and year:

  DAXCopyEditMonthYear = FORMAT([OrderDate], "MMM yyyy")

Example Output: Mar 2025

Conditional Formatting in Labels

You can enhance data labels with conditional text using DAX:

DAXCopyEditSalesStatus = 
IF([Sales] > 1000000, "High Sales ($#,##0)", "Low Sales ($#,##0)")

Example Output:
- High Sales ($1,200,000)
- Low Sales ($850,000)

3. Advanced Format Strings in the Tabular Model

For users working with Power BI models in Analysis Services (SSAS) or Power BI Premium, custom format strings can be applied at the model level.

Open Tabular Editor (if using SSAS or Power BI Premium).
Select the desired measure or column.
Locate the Format String Expression property.
Define a dynamic format string using DAX.

For example, a dynamic currency format based on a selected country:

DAXCopyEditSWITCH(
    [SelectedCurrency],
    "USD", "[$]#,##0.00",
    "EUR", "[€]#,##0.00",
    "GBP", "[£]#,##0.00",
    "JPY", "[¥]#,##0",
    "#,##0.00"
)

This ensures that currency values are displayed in the appropriate format based on the user's selection.

Best Practices for Using Format Strings in Power BI

To maximize the effectiveness of data labels with format strings, follow these best practices:

1. Keep Labels Concise

Use compact formatting for large numbers (e.g., "1.2M" instead of "1,200,000").
Avoid overly detailed labels that clutter visuals.

2. Use Standardized Formatting Across Reports

Ensure consistency in number and date formats across all visuals.
Define a formatting standard for your reports.

3. Apply Conditional Formatting for Better Insights

Highlight important values with custom labels (e.g., “High Sales” vs. “Low Sales”).
Use colors and indicators where possible to enhance visual impact.

4. Avoid Overuse of the FORMAT Function in DAX

The FORMAT function returns text, which can affect sorting and performance.
When possible, apply formatting at the model level instead of within DAX measures.

5. Test Formatting in Different Visuals

Some visuals may not support custom format strings fully.
Verify that labels remain readable in charts, tables, and tooltips.

Publish by : Sneha Tripathi

Date: 5th March 2025

Improving Data Labels with Format Strings in Power BI