Combining DAX Patterns Or DAX Formulas In Power BI

by | 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:

Visualizing total sales versus total sales last year

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.

Writing the formula for cumulative sales by date

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. 

Adding the FILTER function to the cumulative sales formula

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.

The Cumulative Total DAX Pattern

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

Dragging the cumulative sales measure to the 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.

The cumulative sales column calculated by the cumulative DAX formula

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.

An entry showing the cumulative sales from the dates prior up to the current date

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.

Visualizing the cumulative sales column as 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 visualizations for daily total sales and cumulative sales

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.

Copying the cumulative total DAX pattern

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.

Changing inputs within the DAX pattern to calculate cumulative 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. 

The column for cumulative sales last year

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.

Overlaying the visualization for cumulative sales last year on the area chart for cumulative sales

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

The combined visualization of two cumulative total DAX patterns

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.

Comparing visualizations of total sales with and without the use of DAX patterns

***** Related Links*****
Common Time Intelligence DAX Patterns Used In Power BI
Calculating Reverse Cumulative Or Reverse Running Total In Power BI
Manage Multiple Date Calculations In Your Fact Table

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

Related Posts

Understanding Data Models and Visualizations

DAX Table Functions Deep Dive

Explore an in-depth analysis of DAX table functions in Power BI, comparing SUMMARIZE and ADDCOLUMNS, and understanding INTERSECT and EXCEPT for enhanced data manipulation and analysis.