# 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

## The Importance Of Creating Compelling Power BI Visualizations

I think this is a good opportunity to run through why creating great visualizations is so important in...

## Scatter Chart Visualizations With Charticulator

In this tutorial, you'll learn how to create a scatter chart for your Power BI report. This is a chart...

## Learn Power BI With This Dynamic Learning Map

Power BI can help you build dynamic and customizable applications that can be embedded within...

## Dashboard In Power BI: Best Design Practices

In this tutorial, you’ll learn some fundamental design practices that can help you tell a better...

## Using The Remove Function And Setting Up Confirmation Popups

In this tutorial, we're going to talk about how to use the Remove function to create a delete button in...

## Power Automate With Teams To Create Chat Functions

Did you know you can use Power Automate to create chat functions and bots within teams? In this...

## Measure Branching: Power BI Development Strategy

I'd like to share with you a development strategy or concept in Power BI called measure branching. You...

## Power BI Filters: Invoking Context Transitions

This tutorial will discuss about Power BI filters. You'll learn how to see context transition in the...

## Out of Office on Outlook App: A Quick Setup Guide

It's time for your well-deserved yearly vacation, and you want to completely switch off from work. But...

## DAX Language Power BI Tutorial On Reverse TOPN

In this blog post, we are going to take a look at a question that was asked on the Enterprise DNA forum...

## Power BI Values And Literals – Understanding The Basics

In this tutorial, you’ll learn about Power BI Values and how they are generated inside the Power Query...

## Create A Paginated Report: Adding Texts & Images

After getting the data into the Report Builder, the next step is transforming these into a report. In...