Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# DAX Formula Writing Techniques In Power BI

by | 7:00 pm EST | November 02, 2020 | DAX, Power BI

In this tutorial, we’ll talk about DAX formula writing techniques and how to simplify models in Power BI.

We’ll discuss the key functions and methods to organize and make our model’s interface friendlier and easier to operate.

The methods we’re going to go through are formula syntax, measure groups, and formatting DAX codes in Power BI. It’s crucial that we understand these three topics in order to master DAX calculations and formula writing in Power BI.

We’ll discuss each of them and see their unique contributions and importance.

## How A Formula Syntax Works

The formula syntax that we’ll be discussing is a bit different compared to other formula languages. But this is essential for our models in Power BI.

To start off, click the Sales table in the Fields area.

Then, click on New measure.

We’ll call this new measure Total Sales. Next, type SUM.

Information boxes are also present to explain the functions.

With DAX formulas, we have to focus on the tables and columns. Focus on the table we need to reference and the column within it.

Now, input Total Revenue inside the SUM function.

In Power BI, referencing the table always comes first, the column comes after.

However, there are formulas, like the Total Quantity measure, that only require referencing the table.

If we use the COUNTROWS function, we can see that it’s only asking for a table.

Next, reference the Sales table, and we’ll now have another measure that counts the total quantity we sold.

There is another measure in this Power BI example called Total Costs.

For this measure, we use the iterating function SUMX. This function returns the sum of an expression evaluated for each row in a table.

Next, reference the Sales table. Then for the expression, reference the Order Quantity column. Lastly, reference the Sales table again with the Total Unit Cost column.

That is going to give us our Total Costs.

## Importance Of Measure Groups

In Power BI, measure groups are used to organize DAX calculations. It’s highly recommended that we use them in our model as we start creating measures.

For us to run more analyses in our Power BI models, we need to create a lot of measures.

However, creating plenty of measures could give us a busy-looking model. So, we need to create measure groups to avoid mixing our measures and data tables.

Creating a measure group is easy. First, go to Home and then click Enter data.

Next, create a dummy table and name it (Key Measures), then click Load.

That table will then be in our report.

Next, move the other measures into the dummy table, Key Measures. Click the drop-down arrow and select Key Measures.

Once the measures are in the Key Measures table, we can now delete the dummy column or Column1.

Lastly, click the Show/hide pane twice.

It will sort out and change the icon of the measure group like so:

This measure group is just one example. In Power BI, there are other groups like time comparisons group and moving averages.

In some showcase models in Enterprise DNA, we’ll see that we can have 6-8 groups or more.

Utilizing measure groups to organize our measures makes our model look clean and seamless.

## The Value Of Proper DAX Code Formatting

Formatting a formula makes things simple in our model and doesn’t over-complicate our measures in our formulas and reports.

As an example, let’s say this is our Total Quantity and we want to do something advanced.

First, click on New measure.

Then, write Formula Example. Next, use the CALCULATE function and then add in Total Quantity.

After that, use the FILTER function for the Sales table. We’ll make the Customer Name Index greater than 5.

The Customer Name Index must also be less than 20. So, add another FILTER function to go through the Sales. Lastly, we only need to equate the Channel to the Wholesale.

Once we finish writing the DAX formula, we can now see the results. It’s just like the SUM formula in MS Excel, however, it’s a little complicated in Power BI.

### The Use Of Indention For Key Functions

It’s important to format and organize formulas in our model.

It’s also recommended that on different rows, we place different key functions with different indentations. This makes it easy to understand what we’re trying to calculate if the functions are separate.

To do that, hold shift and press enter. Indent and place things on a new row when there is a key formula introduced.

We can see that the function CALCULATE is on a different row as well as the FILTER functions. They are arranged so it can be read easier. This avoids confusion when making analyses for our model.

If we want to use advanced formulas like churn analytics, customer attrition, or even the IF statement, start off by indenting them. This way we can clearly see the flow of our calculation.

We just have to keep indenting every time we add more key functions. We won’t be disoriented with the dozens of calculations in our model.

Implementing DAX Measure Groups Into Your Reports – A Power BI Modeling Review
Introduction To Filter Context In Power BI
How To Use The COUNTROWS DAX Function In Virtual Tables

## Conclusion

In Power BI, we encounter a lot of models, functions, and formulas. With these writing formula techniques, we can improve how the calculations and models are presented.

What we’ve learned here are just some methods, like measure grouping and formatting DAX calculations, to help compute, simplify, and organize our analyses efficiently for a better presentation.

We can use them to have a better grasp of what’s happening in our data model.

All the best,

Sam

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## How to Interpolate in Excel: User Guide With Examples

In data analysis, interpolation plays a crucial role in estimating values that fall between known data...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## How to Move Files in SharePoint: Top 4 Methods Explained

Struggling to move Files in SharePoint? Don't worry, we've all been there. Knowing how to move files...

## How to Return List in Python: 3 Top Methods Explained

The return statement plays a pivotal role in Python. It’s what lets your functions communicate with the...

## How to Create P Value in Excel? A Step-by-Step Guide

Ready to create a P value in Excel? Want to learn how to quickly and efficiently do it? Read on. To...

## How to Strikethrough Text in Excel? Easy Guide + Examples

Want to strikethrough some text in your Excel document but don’t know how? Don’t worry, it's easy, read...

## How To Calculate Z-Scores in Excel? User Guide with Examples

Ready to calculate a z-score in Excel? Awesome. Because we are going to show how to do it quickly and...

## Top 20+ Data Visualization Interview Questions Explained

So, you’re applying for a data visualization or data analytics job? We get it, job interviews can be...

## Top 10 Free Datasets for Statistics Projects

Are you looking for a way to explore and analyze real-world data to enhance your understanding of...

## How to Calculate Standard Error in Excel? Top 3 Methods

The standard error is a key statistical tool that allows you to assess the accuracy and reliability of...