Exploring the Power of the pbiFormat() Function in Deneb

pbiFormat(): A function to format data into a custom format in Deneb, allowing users to use Power BI format strings instead of the D3 format convention. This means you can customize how numbers and dates are displayed in your charts.

Basic Example in Vega-Lite:

{
  "data": {"name": "dataset"},
  "transform": [
    {
      "calculate": "pbiFormat(1980.126, '#,0.00')",
      "as": "Formatted Positive"
    },
    {
      "calculate": "pbiFormat(-1980.1, '#,0.00')",
      "as": "Formatted Negative"
    },
    {
      "calculate": "pbiFormat(0, '#,0.00')",
      "as": "Formatted Zero"
    },
    {
      "calculate": "pbiFormat(null, '#,0.00')",
      "as": "Formatted Blank"
    },
    {
      "calculate": "pbiFormat(1980.126, '#,0.00;(#,0.00)')",
      "as": "Formatted Positive Negative"
    },
    {
      "calculate": "pbiFormat(-1980.1, '#,0.00;(#,0.00)')",
      "as": "Formatted Negative Positive"
    },
    {
      "calculate": "pbiFormat(0, '#,0.00;(#,0.00)')",
      "as": "Formatted Zero Negative Positive"
    },
    {
      "calculate": "pbiFormat(null, '#,0.00;(#,0.00)')",
      "as": "Formatted Blank Negative Positive"
    },
    {
      "calculate": "pbiFormat(1980.12, '#,#.##;(#,#.##);-')",
      "as": "Formatted Positive Custom"
    },
    {
      "calculate": "pbiFormat(-1980.12, '#,#.##;(#,#.##);-')",
      "as": "Formatted Negative Custom"
    },
    {
      "calculate": "pbiFormat(0, '#,#.##;(#,#.##);-')",
      "as": "Formatted Zero Custom"
    },
    {
      "calculate": "pbiFormat(null, '#,#.##;(#,#.##);-')",
      "as": "Formatted Blank Custom"
    },...
  ...
}

The following image illustrates the projected result of applying these formats:

This basic form uses formats utilized in Power BI, which are also employed with the DAX FORMAT function. As seen in the highlighted line, the number is 1980.126, and the format is #,0.00. By specifying two decimal places, the number is rounded up to 1980.13, which is important to understand.

The following table shows the pbiFormat snippet and the format, in case you want to use it, as the previous image does not allow copying the code.

Function	Formatted	Description
pbiFormat(1980.126, '#,0.00')	1,980.13	Positive Number
pbiFormat(-1980.1, '#,0.00')	-1,980.10	Negative Number
pbiFormat(0, '#,0.00')	0.00	Zero Value
pbiFormat(null, '#,0.00')	(Blank)	Blank Value
pbiFormat(1980.126, '#,0.00;(#,0.00)')	1,980.13	Positive with Neg. Format
pbiFormat(-1980.1, '#,0.00;(#,0.00)')	(1,980.10)	Negative with Parentheses
pbiFormat(0, '#,0.00;(#,0.00)')	0.00	Zero with Neg. Format
pbiFormat(null, '#,0.00;(#,0.00)')	(Blank)	Blank with Neg. Format
pbiFormat(1980.12, '#,#.##;(#,#.##);-')	1,980.12	Positive Custom
pbiFormat(-1980.12, '#,#.##;(#,#.##);-')	(1,980.12)	Negative Custom
pbiFormat(0, '#,#.##;(#,#.##);-')	-	Zero Custom
pbiFormat(null, '#,#.##;(#,#.##);-')	(Blank)	Blank Custom

Example of a More Elegant Format

In this section, I present an example of a more elegant data format using the pbiFormat function. This method allows formatting sales values in different scales, from integers to trillions, thus facilitating their visual interpretation.

{
  "data": {"name": "dataset"},
  "transform": [
    {
      "calculate": "pbiFormat(datum['$Sales'],'$#,0')",
      "as": "Formatted Integer"
    },
    {
      "calculate": "pbiFormat(datum['$Sales'],'$#,0,.0#K')",
      "as": "Formatted Thousands"
    },
    {
      "calculate": "pbiFormat(datum['$Sales'],'$#,0,,.0#M')",
      "as": "Formatted Millions"
    },
    {
      "calculate": "pbiFormat(datum['$Sales'],'$#,0,,,.0#B')",
      "as": "Formatted Billions"
    },
    {
      "calculate": "pbiFormat(datum['$Sales'],'$#,0,,,,.0#T')",
      "as": "Formatted Trillions"
    }
  ]
}

The following image illustrates the projected result of applying these formats:

The following table shows the pbiFormat snippet and the format, in case you want to use it, as the previous image does not allow copying the code.

Function	Formatted	Description
pbiFormat(datum['$Sales'],'$#,0')	$1,980	Integer
pbiFormat(datum['$Sales'],'$#,0,.0#K')	$1.98K	Thousands
pbiFormat(datum['$Sales'],'$#,0,,.0#M')	$1.98M	Millions
pbiFormat(datum['$Sales'],'$#,0,,,.0#B')	$1.98B	Billions
pbiFormat(datum['$Sales'],'$#,0,,,,.0#T')	$1.98T	Trillions

Example of Formatting Dates

In this section, we demonstrate how to use the pbiFormat function to format dates in different styles. The following example transforms a dataset by applying various date and time formats.

{
  "data": {"name": "dataset"},
  "transform": [
    {
      "calculate": "pbiFormat(datum['Date'],'dd/MM/yyyy hh:mm:ss')",
      "as": "Formatted DateTime"
    },
    {
      "calculate": "pbiFormat(datum['Date'],'dd/MM/yyyy')",
      "as": "Formatted Date"
    },
    {
      "calculate": "pbiFormat(datum['Date'],'M - yyyy')",
      "as": "Formatted Month Year Single Digit"
    },
    {
      "calculate": "pbiFormat(datum['Date'],'MM - yyyy')",
      "as": "Formatted Month Year Double Digit"
    },
    {
      "calculate": "pbiFormat(datum['Date'],'MMM - yyyy')",
      "as": "Formatted Month Year Abbreviation"
    },
    {
      "calculate": "pbiFormat(datum['Date'],'yyyy')",
      "as": "Formatted Year"
    },
    {
      "calculate": "pbiFormat(datum['Date'],'hh:mm:ss')",
      "as": "Formatted Time"
    },
    {
      "calculate": "pbiFormat(datum['Date'],'M')",
      "as": "Month Single Digit"
    },
    {
      "calculate": "pbiFormat(datum['Date'],'MM')",
      "as": "Month Double Digit"
    },
    {
      "calculate": "pbiFormat(datum['Date'],'MMM')",
      "as": "Month Abbreviation"
    },
    {
      "calculate": "pbiFormat(datum['Date'],'MMMM')",
      "as": "Month Full"
    }
  ]
}

The following graphic shows the projected result of applying these formats:

The following table shows the pbiFormat snippet and the format, in case you want to use it, as the previous image does not allow copying the code.

Function	Formatted	Description
pbiFormat(datum['Date'], 'dd/MM/yyyy hh:mm:ss')	02/01/2020 03:30:00	Formatted DateTime
pbiFormat(datum['Date'], 'dd/MM/yyyy')	02/01/2020	Formatted Date
pbiFormat(datum['Date'], 'M - yyyy')	1 - 2020	Formatted Month Year Single Digit
pbiFormat(datum['Date'], 'MM - yyyy')	01 - 2020	Formatted Month Year Double Digit
pbiFormat(datum['Date'], 'MMM - yyyy')	Jan - 2020	Formatted Month Year Abbreviation
pbiFormat(datum['Date'], 'yyyy')	2020	Formatted Year
pbiFormat(datum['Date'], 'hh:mm:ss')	03:30:00	Formatted Time
pbiFormat(datum['Date'], 'M')	1	Month Single Digit
pbiFormat(datum['Date'], 'MM')	01	Month Double Digit
pbiFormat(datum['Date'], 'MMM')	Jan	Month Abbreviation
pbiFormat(datum['Date'], 'MMMM')	January	Month Full

Note: While exploring the use of this function in Deneb, I discovered that when formatting the month in any of its presentations (full month, abbreviated month, etc.), the letter “M” in the format string must be uppercase. As of writing this article, this behavior differs from the DAX FORMAT function.

Advanced Exploration of the pbiFormat Function

The pbiFormat function offers great versatility when used in expression functions, providing more control compared to direct encoding properties. The complete signature of this function is:

pbiFormat(value, format, options = {})

Here are the key parameters:

Parameter	Description
value	The number to be formatted.
format	A valid Power BI format string.
options	An optional object with additional formatting options.

Important options within the options parameter include:

• format - Custom format string (overrides the format parameter if specified)
• precision - Maximum decimal places
• value - Value for formatting (e.g., 1e3 for thousands, 1e6 for millions)
• cultureSelector - Locale-specific formatting (e.g., en-GB, fr-FR)

Example Implementation in Vega-Lite

The following Vega-Lite specification demonstrates the use of pbiFormat for dynamic number formatting:

{
  "data": {"name": "dataset"},
  "transform": [
    {
      "calculate": "pbiFormat(datum['$Sales'], datum['$Sales__format'], { value : if(datum['$Sales'] >= 1e12, 1e12, if(datum['$Sales'] >= 1e9, 1e9, if(datum['$Sales'] >= 1e6, 1e6, if(datum['$Sales'] >= 1e3, 1e3, 0 )))), precision: datum['Precision Value'] })",
      "as": "SalesFormatted"
    },...
  ],...
}

Explanation of the pbiFormat Function

The pbiFormat function in Deneb is used to format data into custom formats. Here’s a concise breakdown of its usage with an example:

pbiFormat(datum['$Sales'], datum['$Sales__format'], {
    value: if(datum['$Sales'] >= 1e12, 1e12,
              if(datum['$Sales'] >= 1e9, 1e9,
                 if(datum['$Sales'] >= 1e6, 1e6,
                    if(datum['$Sales'] >= 1e3, 1e3, 0)))),
    precision: datum['Precision Value']
})

Key Components

1. datum['$Sales']: The value to be formatted.
2. datum['$Sales__format']: The format string.
3. value: Sets the formatting scale based on conditions:
   • >= 1e12 (trillion): Use 1e12
   • >= 1e9 (billion): Use 1e9
   • >= 1e6 (million): Use 1e6
   • >= 1e3 (thousand): Use 1e3
   • Else: Use 0
4. precision: datum['Precision Value']: A dynamic parameter that adjusts the number of decimal places displayed.

Note

For this example, I’ve used dynamic parameters for both the format and precision. However, you can use fixed formats, such as ‘#0,0.00’, and set precision values like 1, 2, or 3, depending on your needs. As mentioned earlier, these parameters are optional, and the function can be used without them.

Example Output

The formatted results are displayed as:

• 1000000000026 ➔ $1.0T
• 1000000026 ➔ $1.0bn
• 1000026 ➔ $1.0M
• 1026 ➔ $1.0K
• 926 ➔ $926.0

This example illustrates how pbiFormat dynamically formats sales values into readable scales based on their magnitude, as shown in the following gif image.

Example of Date Formatting by Culture

In this example, we demonstrate how to format a date using different cultural settings. We use the pbiFormat function to transform the date into various region-specific formats. Below is the sample code:

{
  "data": {
    "name": "dataset"
  },
  "transform": [
    {
      "calculate": "pbiFormat(datum['Date'], 'mm/dd/yyyy', {format: 'dd MMMM yyyy', cultureSelector: 'pt-BR'})",
      "as": "Formatted_Brazil_pt-BR"
    },
    {
      "calculate": "pbiFormat(datum['Date'], 'mm/dd/yyyy', {format: 'dd MMMM yyyy', cultureSelector: 'ru-RU'})",
      "as": "Formatted_Russia_ru-RU"
    },
    {
      "calculate": "pbiFormat(datum['Date'], 'mm/dd/yyyy', {format: 'dd MMMM yyyy', cultureSelector: 'en-US'})",
      "as": "Formatted_USA_en-US"
    },
    {
      "calculate": "pbiFormat(datum['Date'], 'mm/dd/yyyy', {format: 'MM/dd/yyyy', cultureSelector: 'en-GB'})",
      "as": "Formatted_UK_en-GB"
    },
    {
      "calculate": "pbiFormat(datum['Date'], '', {format: 'MM/dd/yyyy hh:mm:ss a', cultureSelector: 'en-US'})",
      "as": "Formatted_USA_With_Time_en-US"
    },
    {
      "calculate": "pbiFormat(datum['Date'], '', {format: 'dd/MM/yyyy HH:mm:ss', cultureSelector: 'en-GB'})",
      "as": "Formatted_UK_With_Time_en-GB"
    },...
  ],...
}

In this code:

• Formatted_Brazil_pt-BR: Formats the date as dd MMMM yyyy with the pt-BR (Brazil) culture.
• Formatted_Russia_ru-RU: Formats the date as dd MMMM yyyy with the ru-RU (Russia) culture.
• Formatted_USA_en-US: Formats the date as dd MMMM yyyy with the en-US (United States) culture.
• Formatted_UK_en-GB: Formats the date as MM/dd/yyyy with the en-GB (United Kingdom) culture.
• Formatted_USA_With_Time_en-US: Formats the date and time as MM/dd/yyyy hh:mm:ss a with the en-US (United States) culture.
• Formatted_UK_With_Time_en-GB: Formats the date and time as dd/MM/yyyy HH:mm:ss with the en-GB (United Kingdom) culture.

This approach ensures that dates are consistently and culturally accurately represented, which is crucial for international applications.

Function	Formatted	Description
pbiFormat(datum['Date'], 'mm/dd/yyyy', {format: 'dd MMMM yyyy', cultureSelector: 'pt-BR'})	02 janeiro 2020	Formatted_Brazil_pt-BR
pbiFormat(datum['Date'], 'mm/dd/yyyy', {format: 'dd MMMM yyyy', cultureSelector: 'ru-RU'})	02 января 2020	Formatted_Russia_ru-RU
pbiFormat(datum['Date'], 'mm/dd/yyyy', {format: 'dd MMMM yyyy', cultureSelector: 'en-US'})	02 January 2020	Formatted_USA_en-US
pbiFormat(datum['Date'], 'mm/dd/yyyy', {format: 'MM/dd/yyyy', cultureSelector: 'en-GB'})	01/02/2020	Formatted_UK_en-GB
pbiFormat(datum['Date'], '', {format: 'MM/dd/yyyy hh:mm:ss a', cultureSelector: 'en-US'})	1/2/2020 3:30:00 PM	Formatted_USA_With_Time_en-US
pbiFormat(datum['Date'], '', {format: 'dd/MM/yyyy HH:mm:ss', cultureSelector: 'en-GB'})	02/01/2020 15:30:00	Formatted_UK_With_Time_en-GB

Download the Files Used Here

🔽 pbiFormat_Function.pbix (3.47 MB)

---

Conclusion

In conclusion, the pbiFormat function is incredibly powerful for dynamically formatting values. This capability allows us to define templates with all necessary logic and reuse them across our projects, eliminating the need to manually format our visuals each time. In this academic case, I have only demonstrated some common examples, but there are many more possibilities worth exploring. Additionally, we haven’t covered formatting for X or Y axes, which can be done simply in Vega-Lite, but Vega allows you to leverage the full potential of this function.

References

For more detailed information and advanced usage, you can refer to the following resources:

• D3 Format
• Power BI Custom Format Strings
• Deneb Formatting
• DAX FORMAT Function