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
