FORMAT

How to Use the Power BI DAX function FORMAT

In this article, we will explore how to use the Power BI DAX function FORMAT to format numbers, dates, and text. We will also discuss some common formatting scenarios that you may encounter in your Power BI projects.

Formatting Numbers with the FORMAT Function

The FORMAT function in Power BI allows you to format numbers in a variety of ways. You can use it to display currency, percentages, and decimal places, among other things.

To use the FORMAT function to format a number, you must first specify the number that you want to format and then provide a format string that describes the format you want to apply.

Here’s an example of how to use the FORMAT function to format a number as currency:

FORMAT(1234.56,”$#,##0.00″)

In this example, the number 1234.56 is formatted as currency using the format string "$#,##0.00". The resulting output is "$1,234.56".

You can also use the FORMAT function to format numbers as percentages, decimals, and scientific notation, among other things. For a complete list of format strings that you can use with the FORMAT function, refer to the Power BI documentation.

Formatting Dates with the FORMAT Function

Just like with numbers, the FORMAT function can also be used to format dates in Power BI. You can use it to display dates in a variety of formats, including month/day/year, day/month/year, and year/month/day.

To use the FORMAT function to format a date, you must first specify the date that you want to format and then provide a format string that describes the format you want to apply.

Here's an example of how to use the FORMAT function to format a date:

FORMAT(DATE(2021,1,1),”MM-dd-yyyy”)

In this example, the date January 1, 2021, is formatted as "MM-dd-yyyy" using the FORMAT function. The resulting output is "01-01-2021".

You can also use the FORMAT function to format dates as day names, month names, and year numbers, among other things. For a complete list of format strings that you can use with the FORMAT function, refer to the Power BI documentation.

Formatting Text with the FORMAT Function

In addition to numbers and dates, the FORMAT function can also be used to format text in Power BI. You can use it to display text in a variety of ways, including uppercase, lowercase, and proper case.

To use the FORMAT function to format text, you must first specify the text that you want to format and then provide a format string that describes the format you want to apply.

Here's an example of how to use the FORMAT function to format text:

FORMAT(“hello world”,”UPPER”)

In this example, the text "hello world" is formatted as uppercase using the FORMAT function. The resulting output is "HELLO WORLD".

You can also use the FORMAT function to format text as lowercase, proper case, and title case, among other things. For a complete list of format strings that you can use with the FORMAT function, refer to the Power BI documentation.

Common Formatting Scenarios

Now that you know how to use the Power BI DAX function FORMAT, let's explore some common formatting scenarios that you may encounter in your Power BI projects.

Formatting a Number as a Percentage

To format a number as a percentage, use the FORMAT function with the "0.00%" format string. For example:

FORMAT(0.45,”0.00%”)

The resulting output is "45.00%".

Formatting a Date as Month/Year

To format a date as month/year, use the FORMAT function with the "MM/yyyy" format string. For example:

FORMAT(DATE(2021,1,1),”MM/yyyy”)

The resulting output is "01/2021".

Formatting Text as Proper Case

To format text as proper case, use the FORMAT function with the "Proper" format string. For example:

FORMAT(“hello world”,”Proper”)

The resulting output is "Hello World".

The Power BI DAX function FORMAT is a powerful tool that allows you to format numbers, dates, and text in various ways. By using the FORMAT function, you can create custom formats and display your data in a more meaningful way. We hope that this article has provided you with a good understanding of how to use the FORMAT function in Power BI to format your data.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)