# 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

## Debugging DAX: Tips and Tools for Troubleshooting Your Formulas

One of the main reasons why businesses all over the world have fallen in love with Power BI is because...

## Practical Application of TREATAS Function in DAX

A hands-on project focused on using the TREATAS function to manipulate and analyze data in DAX.

## MAXX in Power BI – A Detailed Guide

A hands-on guide to implementing data analysis projects using DAX, focused on the MAXX function and its combinations with other essential DAX functions.

## Leveraging the COUNTX Function In Power BI

Learn how to leverage the COUNTX function in DAX for in-depth data analysis. This guide provides step-by-step instructions and practical examples.

## Using the FILTER Function in DAX – A Detailed Guide With Examples

A comprehensive guide to understanding and implementing the FILTER function in DAX, complete with examples and combinations with other functions.

## DATESINPERIOD Function in DAX – A Detailed Guide

Learn how to implement and utilize DAX functions effectively, with a focus on the DATESINPERIOD function.

## Using the DISTINCT Function Effectively in DAX

A systematic exploration of the DAX DISTINCT function to optimize data analytics.

## Guide and Many Examples – ALL Function in DAX

A detailed guide to understanding, implementing, and mastering the DAX ALL function, complemented by practical examples and combinatory techniques.

## Detailed Guide to SWITCH function in DAX

A comprehensive guide to mastering DAX functions in Power BI for conducting advanced data analysis.

## SUMMARIZE Function in DAX – A Deep Dive

A comprehensive guide to using the DAX function SUMMARIZE in Power BI, with detailed explanations and practical examples.

## Your Data Visualization Doesnâ€™t Look Great. What Should You Do?

Data visualization is the key to unlocking the insights hidden within your data. But, what if your...

## Leveraging Power BI for Data-Driven Decisions

In the world of data analytics, thereâ€™s a constant demand for tools that not only help you make sense...