Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# Combining DAX Patterns Or DAX Formulas In Power BI

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

For this tutorial, I’ll be showing you how to combine multiple DAX patterns in Power BI. This strategy is one of the many applications of measure branching and is an effective way of producing high-quality reports. You may watch the full video of this tutorial at the bottom of this blog.

When your goal is to analyze trends over time, sticking to basic formulas may not always give you valuable insights.

For example, if you want to compare the Total Sales of this year versus the previous year, plotting values as-is will look something like this:

As you can see, the graph is a little busy. It doesn’t really showcase the information we want.

This is when combining patterns of DAX formulas come in.

What DAX patterns do is they shape data in a way that yields meaningful data models and visualizations. When you combine multiple patterns of DAX formulas, you can present your data better and gain useful insights.

I’ll be demonstrating this by improving the graph above by combining patterns of DAX formulas.

## The Cumulative Total DAX Pattern

I decided to go with finding the Cumulative Total Sales by Date.

First. I’m going to create a new measure and use the CALCULATE function for Total Sales.

Next, I’m going to write an additional layer of formula. In this case, I need the FILTER function inside of CALCULATE and I’ll be pairing this with the ALLSELECTED function.

The FILTER function sorts data to identify from which fields I want my values returned.

Meanwhile, the ALLSELECTED function tells Power BI to only include data that is within a set time frame. That is, whatever specific range of dates I enter.

The most important part of the formula is defining how the FILTER function works. For Cumulative Sales, the goal is to show the partial sum of Total Sales as of date.

To do this, I specified that Total Sales should be calculated from dates that are less than or equal to the MAX date.

As a result, the FILTER function enables you to look at the current date as well as the dates prior, then evaluate if the current date is less than or equal to the MAX date.

This entire formula is the Cumulative Total DAX Pattern.

Once everything is set, hit enter and drag the measure into your table.

I like to fix my formatting before proceeding just so everything is uniform and logical. Here, I tweaked the format to make the values appear as currency.

What I have here now is the Cumulative Sales column. You can see that every value reflects the running total up to a certain date.

So if I were to manually add up the total sales from June 3 to June 10, 2019, I would get \$446,387, which is exactly what it says on the Cumulative Sales column.

At this point, I can already show you how much better this data will look visually compared to the earlier graph.

Copy the Cumulative Sales column and make it into an area chart.

Right away, you see there is a massive difference between the two visualizations. The Cumulative Sales area chart provides a clearer understanding of how things have gone over time.

## Comparing Trends Using Combined Patterns of DAX Formulas

Of course, to compare trends you need at least two sets of values.

To get the Cumulative Sales of last year, I’m just going to copy the first Cumulative Total DAX Pattern and create a new measure.

Then, I’m going to paste the formula and rename the measure to Cumulative Sales Last Year

Because this is a DAX pattern, there’s no need to change anything within the formula combination itself. All I have to do is modify the input around the measure.

In this case, I’m just going to replace Total Sales to Sales Last Year.

Again, hit enter and drag the measure to your table. You should end up with a column of Cumulative Sales Last Year

This follows the same logic of the previous formula, wherein the values shown are the running totals up to the current date.

Finally, you can overlay this measure’s visualization onto the Cumulative Sales area chart.

Just select the previous chart and drag and drop and data field into the Values tab.

The result is an excellent visualization comparing trends from different time periods.

Before I wrap this up, I want to show you again how the first graph looked with just the daily sales versus what I have now.

Combining patterns of DAX formulas gave me richer insights. It also made it easier to see trends and compare them from what was seen at a different time.

## Conclusion

Combining patterns of DAX formulas is the best way to analyze and compare trends when dealing with time-related information. It makes use of DAX patterns, which are a big part of scaling up your analytical work because of their re-usability.

What I did here is just a basic example of how you can leverage measure branching and combining patterns of DAX formulas. Obviously, this technique is not limited to Cumulative Totals

More complicated calculations start simple and you can slowly build and branch out from those basic formulas to do more exciting analyses.

I hope this tutorial has given you a good idea of how to do that and inspired you to explore its potential.

All the best!

Sam

## How to Delete a SharePoint Site: 4 Best Methods Explained

Ready to delete your SharePoint site? It really is as easy as 1,2,3 and 4! To delete a SharePoint site:...

## How to Unsync a SharePoint Folder: A Step-by-Step Guide

In today's fast-paced digital world, syncing folders in SharePoint to your desktop can make...

## Datasets for Projects: Top 20 Free Datasets for Data Science

Data is at the heart of every great project. Whether analyzing customer trends or training a machine...

## How to Convert List to a Dataframe in Python: 10 Top Ways

As a data scientist or analyst, at times, you will need to convert a list to a dataframe in Python....

## Can Excel Do Algebra? It Sure Can: User Guide + Examples

Sometimes, Excel requires you to break out your math skills. But before you get scared, don't worry;...

## How to Convert Set to List in Python: 9 Top Methods Explained

Converting a set to a list is a common task for Python developers. If you're looking to do this, you're...

## How to Make a List From 1 to 100 in Python: 7 Top Ways + Code

Ready to make a list from 1-100 quickly and efficiently, well you're in the right place! To make a list...

## Python: Counts How Many Occurrences in a List 6 Top Ways

If you are a coder who knows Python, you might be aware of one of its best features: its ability to...

## How to Prepend to a List in Python: 6 Best Methods Explained

Ready to learn how to prepend to a list? We have 6 great methods to show you today. Lists are important...

## Master’s in Data Science Salary Expectations Explained

Are you pursuing a Master's in Data Science or recently graduated? Great! Having your Master's offers...

## Can Excel Pull Data from a Website? Top 3 Ways Explained

Are you still copying and pasting when you need to bring data from a website to Excel? Well, you don't...

## 6 Ways How to Make a String Uppercase in Python

So, you want to make a string uppercase in Python? That's good, because we will show you how, and it's...