# Optimize Power BI Formulas Using Advanced DAX

I’ll go over some interesting concepts that involve using several table functions to optimize your Power BI formulas. You may watch the full video of this tutorial at the bottom of this blog.

Iâ€™m going to use an example from the Enterprise DNA Support Forum where one of the members asked if it’s possible to add a filter while using the SUMMARIZE function.

This is quite common for scenarios when you need to create virtual tables that iterate through a logic within the measures instead of iterating the entire table.

The SUMMARIZE function has a lot of usages. It enables you to create a summary table, either physically or virtually. You can also use it to create more advanced table functions by adding ADDCOLUMNS, CROSSJOIN, or INTERSECT. Thereâ€™s a wide range of these table functions that you can utilize.

For this tutorial, I want to show you how to combine some table functions to optimize your Power BI formulas. This way, you can get more relevant results that you need to use for your analysis.

## Optimizing Power BI Formulas Using A Filter Function

In the example, the member wanted to create a table of sales by city, but only wanted to look at a certain subset of those cities. Thus, an additional filter is needed in the table to obtain a certain range of sales.

One of our Enterprise DNA experts, Brian, came up with a relatively simple solution to solve this scenario. The key point of the solution is that you can put a virtual table after placing the FILTER function.

In this case, you can utilize any table function that is available to you, such as SUMMARIZE. The SUMMARIZE function is a table function that creates a summary table for regions and cities. In addition to that, it contains the total city sales and the total sales.

Since this virtual table is inside a filter, it only returns those cities which have over 1 million total city sales.

As you can see, it works well and it’s effective in optimizing your Power BI formulas.

Moreover, you can also create a measure that only looks for the average sales per city using AVERAGEX. This is useful in a scenario where you only want to look at the sales for the top cities over a certain amount.

The AVERAGEX function can help minimize the amount of iterations needed for your formula. This function also helps in letting the virtual table iterate through smaller or lesser numbers.

## Using ADDCOLUMNS To Optimize Power BI Formulas

Another member, Nick, contributed a better way to optimize the sample Power BI formula. It involves layering another table function called ADDCOLUMNS inside of FILTER instead of just having the SUMMARIZE function.

The new technique slightly improves the performance of the calculation and prevents some odd results. The ADDCOLUMNS function enables you to create a virtual table with additional columns.

For this example, the SUMMARIZE function creates a one-column table. But through ADDCOLUMNS, you can add another column for total sales.

Just put the ADDCOLUMNS formula within the FILTER function in an existing formula to create an additional filter. This is just one way to create a two-column and filtered table that looks like the one below.

You can also utilize the same technique to achieve any kind of virtual table that iterates through an AVERAGEX function or SUMX or MINX function. You can just layer these table functions on top of each other to change the shape of the virtual table.

***** Related Course Modules*****
DAX Formula Deep Dives
Mastering DAX Calculations
Ultimate Beginners Guide to DAX

## Conclusion

You can optimize your Power BI formulas to change the shape or adjust the summary of data in your model by using the power of DAX.

In this brief tutorial, I just want to highlight some great solutions from the members in Enterprise DNA Forum. As I have shown, there were some interesting techniques that I think you can easily apply to your own measures and logic inside Power BI. I hope you’ve picked up some useful tips here today.

All the best!

Sam

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## Building Your Data Model Relationships In Power BI

In today's tutorial, I want to highlight how important it is to build your data model relationships...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## Discover Unique Insights Using Power BI TOPN Function

In this video, I wanted to find an insight that was really unique, and hugely valuable for business....

## A New Look At CALCULATE – Power BI

In this tutorial, we'll be looking at how to use the DAX function CALCULATE in Power BI. You can watch...

## Ranking Visualization In Power BI – Dynamic Visual

In this blog post, I want to teach you how to show your customers moving through groups over time in a...

## Ranking Insights Using TOPN In Power BI

In this article, we will explore how we can use the TOPN function in Power BI. You will really enjoy...

## Data Storytelling And Important Questions To Ask

When it comes to data storytelling, there are three important factors to consider to make sure that our...

Many times when you get into more advanced formula with DAX, you'll find that the totals that show up...

## Query Editor In Power BI | An Overview

In this tutorial, weâ€™ll show a detailed example of why we always need to use the Query Editor in Power...

## Utilizing Measures And Visualizations In Power BI

In this tutorial, you'll learn about the essential things that need to be prepared in making a...

## Power BI Feature Bookmark Navigator: Tutorial & Review

As part of the November 2021 Power BI updates, Microsoft included a new Power BI feature called...

## Adding Workday And Weekend Numbers Into Your Date Table: Time Intelligence Technique In Power BI

This is a very unique example of Time Intelligence in Power BI, especially when working with workdays...