# 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.

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.

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.

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

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

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.

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.

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

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.

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.

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 Online.

Good luck with this one!

Sam

[youtube https://www.youtube.com/watch?v=-pWvjcUTz_U&w=784&h=441]

## PowerApps Documentation: Using MS Docs For Expert Functionalities

Let's talk about Microsoft's PowerApps documentation and what an important resource it can be for users...

## Data Modeling In Power BI: Tips & Best Practices

In this tutorial, you'll learn valuable tips and best practices for data modeling in Power BI. You can...

## Using The Query Builder Feature In DAX Studio

In this tutorial, you’ll learn how to use the Query Builder feature to easily create queries and...

## Time-Related Insights From Your Supply Chain Metric

For this tutorial, I'm going to cover some high-quality time-related insights directly from your supply...

## Create A Multilingual Power BI Report

For today's blog, I want to discuss a not-unusual situation with many of my consulting assignments for...

## CROSSJOIN DAX Function: Server Timings & Query Plan

In this tutorial, you’ll learn how the CROSSJOIN function works in DAX using the Server Timings pane...

## Overview Of The DAX Studio Keyword COLUMN

Another important keyword to learn when using DAX Studio is the COLUMN keyword. In simplest terms, the...

## Python Cheat Sheet: Essential Quick and Easy Guide

When programming in Python, having a reliable cheat sheet by your side can be a game-changer. Python...

## 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...