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 **S**UMMARIZE 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