2 Ways to Format DAX Code in Power BI

Mastering the art of writing clean and well-formatted code is essential for any data professional working with Power BI and Analysis Services.

When it comes to DAX (Data Analysis Expressions), proper code formatting not only improves the readability of your formulas but also enhances collaboration and the maintainability of your projects.

DAX is a powerful language for creating calculations and measures in Power BI, Power Pivot, and Analysis Services Tabular. However, DAX code can quickly become difficult to read and maintain when it’s not formatted properly.

In this post, you will learn some of the best practices for formatting DAX code.

DAX Formatting Best Practices

When writing DAX code, here are some formatting best practices you should keep in mind.

Spaces Between Operators

1. Always add space between an operand and an operator: when writing an expression in DAX code, it is recommended to include a space before and after any operand (a value or variable) and operator (such as +, -, *, /) used within the expression.

This practice helps improve the clarity and readability of the code.

Here’s an example that demonstrates this guideline.

TotalSales=SUM(SalesAmount)+ProfitMargin/2

DAX expression without spaces.

TotalSales = SUM(SalesAmount) + ProfitMargin / 2

DAX expression with spaces.

Splitting Expressions

If you split an expression into multiple lines, the operator should be the first character in a new line: To maintain clarity and readability, when splitting an expression into multiple rows, you should begin a new line with the operator as the first character.

This practice helps indicate the continuation of the expression and improves the overall organization of the code.

Here’s an example to demonstrate this guideline:

TotalSales = SUM(SalesAmount) + ProfitMargin /
 2 – Discount

Without proper line continuation.

TotalSales = SUM(SalesAmount) 
+ ProfitMargin 
/ 2 
- Discount

With proper line continuation.

Spaces Before Arguments

Add a space before every argument when you write an expression on a single line: When writing DAX code, it is important to include a space before an argument if you write it on the same line.

This practice enhances code readability and helps distinguish between different elements within the expression.

Let’s take a look at an example to illustrate this.

SalesAmount=SUM('Sales'[Amount])-IF('Sales'[Category]="Electronics",10,0)

Without space before the arguments.

SalesAmount = SUM('Sales'[Amount]) - IF('Sales'[Category] = "Electronics", 10, 0)

With spaces before the argument.

Multiple Arguments on Multiple Lines

4. When dealing with expressions that have more than two arguments or involve function calls, it is generally recommended to write them on multiple lines for improved clarity and readability.

When formatting such expressions across multiple lines, follow these guidelines.

  • Place the opening parenthesis ( on the same line as the function call.
  • Start each argument on a new line, indented 4 spaces from the beginning of the function call.
  • Align the closing parenthesis ) with the beginning of the function call.
  • Put the comma separating two arguments on the same line as the previous argument, without any spaces before it.

Here’s an example of how this guideline will format a CALCULATE expression.

CALCULATE(
    SUM(Sales),
    FilterTable[Region] = "North"
)

When following these guidelines to format expressions written in multiple lines, you can use Shift + Enter to go to a new line. This will also automatically add indents to every argument in the expression.

DAX Formatting with DAX Formatter

Formatting DAX code can be made easier by utilizing the DAX Formatter online tool. Developed by SQLBI, this free tool is designed to format your DAX code according to the guidelines mentioned earlier.

DAX Formatter on the Web

By accessing the DAX Formatter tool, you can effortlessly format your DAX code and ensure it adheres to the recommended guidelines for improved readability and consistency.

The DAX Formatter online tool offers a range of options to customize the formatting of your DAX code according to your preferences.

By clicking on the SETTINGS button, you can easily adjust separators, spacing, and line settings.

Utilizing this tool is straightforward: just paste your unformatted DAX code into the designated text box, and with a click on the FORMAT button, you can transform it into a neatly formatted version.

This tool simplifies the process of enhancing the readability and structure of your DAX code, saving you time and effort.

Using most of the guidelines, the formatting tool gives clarity and organization to your DAX code. Once formatted, you can copy the code, obtain an HTML version, save it as a .doc file, or even edit and format a new DAX code using the buttons available in the tab located below the text box. These features empower you to efficiently work with your DAX code and ensure consistency in its presentation.

Dax Formatter in DAX Studio

The DAX Formatter tool is also available in DAX Studio. You will find it – Format Query – under the Home tab.

To use the Format Query, paste or write your syntax in the DAX studio editor.

After pasting your DAX code into the editor, simply click on the Format Query button to format it. Alternatively, you can use the F6 function key to format the selected code or the entire code in the editor.

To select between a short line or long line format style, simply click on the dropdown arrow.

By default, the formatting tool applies the short line code format. However, if you prefer the long line format, you can use the shortcut keys Ctrl + F6 to apply it.

With this, you can easily switch between different code formatting options, providing flexibility and convenience in aligning your code according to your preferred style.

You can also modify this by following these steps.

  1. Click on the File ribbon.
  2. Select Options from the dropdown menu.
  3. Locate and click on the DAX Formatter option.

Conclusions

By following the formatting guidelines such as including spaces before operands and operators, placing arguments on separate lines with proper indentation, and utilizing tools like the DAX Formatter, you can significantly enhance the readability, maintainability, and collaboration of your DAX code.

Well-formatted code not only makes it easier for you to understand and troubleshoot the code but also ensures that other developers can quickly grasp its logic. By investing time and effort into formatting DAX code effectively, you can unlock the full potential of your data analysis projects and elevate your DAX skills.

About the Author

Oluwaseun Olatoye

Oluwaseun Olatoye

Oluwaseun is a business intelligence analyst with expertise in Google Sheets, Excel, Power BI, SQL. He has worked with various businesses to make data-driven decisions. He enjoys helping others learn and grow.

Related Articles

Comments

0 Comments

Get the Latest Tech Tips

Write For Us

Are you a tech enthusiast with a talent for writing great content? Come write for us!

Follow Us

Follow us on social media to stay up to date with the latest in tech!