Virtual Tables Inside Iterating Functions In Power BI – DAX Concepts

In this tutorial, I’m going to cover a very interesting topic around virtual tables, and how you can utilize them in Power BI within iterating functions. You may watch the full video of this tutorial at the bottom of this blog.

Virtual tables are the essential ingredient to creating advanced logic in Power BI. There’s a whole subset of functions inside Power BI that enable you to create these virtual tables.

These virtual tables can sometimes merely be used as filter and functions or to add context to a calculation. But, they also allow you to internally iterate logic through them. It can be based on any context that you placed them into.

A lot of the power of these virtual tables comes when you utilize them with various iterating functions. You have to really understand ‘context’ and how the combination of these DAX measures all work together within that particular context.

Iterating functions in DAX generally has an X on the end, like SUMX, AVERAGEX and many other derivatives of the X formulas in Power BI.

The best way to explain the concept that I want to discuss in this tutorial is through some examples using this simple model.

sample Power BI model for understanding virtual tables

From my Locations table, I have a relationship which flows down to my Sales table. This may seem so generic and you may be wondering how you can apply this kind of model.

Well, in reality, all data is so similar. It’s just a matter of setting up your model well and setting it up in an intuitive way. If you need to understand your modeling a little bit better, you can check out our advanced modeling course here.

Table of Contents

Simple Logic Using SUMX

Let’s check out this simple logic where you can calculate Total Sales using SUMX.

Generally, it’s just calculating our sales for every single region. In this case, we have no other filters on our data. So, it’s just basically from the beginning of time along with the Total Sales.

result of the sample logic for the SUMX function

However, I want to show you something a little bit more unique in terms of how we can iterate logic through these virtual tables.

Using FILTER For Virtual Tables

There can be times when you might want to start calculating different things. What you might want to do is to calculate the sales of what can be classified as a good customer.

sample logic for calculating the Sales of Good Customers

Let’s try to analyze this particular formula and identify what it allows us to do.

sample logic using the FILTER function for creating virtual tables within SUMX

So, you’ll see here that we’re using SUMX.

using SUMX function for calculating the Sales of Good Customers

But, with this part of the measure, we are altering the virtual table that we are using as context for the calculation.

So, you always need to remember that any calculation in Power BI happens in a two-step process. First is the processing of the initial context. And then, there’s the measure calculation. It’s definitely a very simplified version.

Let’s have a look at this first result where the first filter is Connecticut. Obviously, there’s already some filtering that’s happening behind the model.

first filter from the sample table of results

Furthermore, the proceeding logic within the FILTER function creates a virtual table of all the customers who have purchased in Connecticut.

It’s basically just a one-column table of all the customers who have purchased in Connecticut.

Then, within this particular virtual table, we are running a logic which will filter out every single customer that has purchased under 2000. This will only retain those customers that have purchased over 2000.

a logic within a virtual table which will filter out every single customer that has purchased under 2000, and will retain those customers that have purchased over 2000

Based on this new table, we are finally going to calculate the Total Sales.

calculating the Total Sales using a virtual table within SUMX

And that’s what SUMX allows us to do. It’s really a technique that you can hopefully implement in various ways.

Altering The Logic For Virtual Tables

Additionally, you can alter the existing logic. As you can see, this number is currently static. But you can make it dynamic and you can self-generate it.

In this case, I just changed it to 5,000. And because we used SUMX, this table will only look for those good customers that have bought over 5000. And then it will count up the sales from those good customers.

Using SUMMARIZE For Virtual Tables

Here’s another example that you can take up to another level.

The table within the FILTER function can be very different and can be a more detailed table. In this case, we’re looking at both the Sales of Good Customers and the Products they buy.

So, this won’t be a one-column virtual table anymore. This will be a two-column virtual table of every single customer and every single product that they bought in Connecticut. And then, it changes as you go down to different regions or different states.

a two-column virtual table of every single customer and every single product that they bought in Connecticut

Using the SUMMARIZE function, we’ll filter out all the customers and product sales that are less than 2000. Then only retain the ones that are above 2000.

using the SUMMARIZE function for filtering a virtual table within SUMX

After that, we’ll calculate the Total Sales using SUMX.

And that is actually how you can internally iterate some logic through a virtual table and evaluate the particular results.

Using CALCULATE As A Filter

Moreover, you can calculate the same scenario in another way, and it will still give you the same result. Let’s first turn this back to 5000.

Now, you see here that the results in these two columns are actually the same now. But I’ve calculated it in a slightly different way.

For the Good Customer Sales measure, we used the CALCULATE function instead of SUMX.

The CALCULATE function enables you to do a similar thing with our previous SUMX scenario. But, instead of being an iterating function (like with SUMX), it’s actually been used as a filter. It was used to change the context of the calculation within CALCULATE.

And that’s another way of how you can apply this logic in your data models.

Conclusion

This is a really good tutorial to review in depth. Understanding this concept of iterating logic through a virtual table will give you endless analytical possibilities that you can achieve in any data.

If you can understand this well, you will start seeing that there is really nothing from an analytical perspective that you cannot discover when utilizing Power BI and DAX measures very well.

If you want to learn more about combining multiple DAX functions together for optimal effect, check out the Advanced DAX Combinations module at Enterprise DNA Onlin e.

Good luck with this one!

Sam

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events