Examples Of Virtual Tables & Their Importance To A...

Today, I want to touch on a really fascinating topic inside of Power BI. I call this one virtual tables.

If you can start understanding how to incorporate virtual tables inside your DAX functions, you will quickly see the opportunities to extend your analysis even further.

Virtual tables enable you to work out really advanced insights inside your Power BI reports but do it in a relatively seamless and intuitive way.

I will run through a few examples within this post. By doing so, I feel like you’ll have a far greater understanding of what virtual table are and how you can actually utilize them inside of your Power BI reports.

The CROSSJOIN function is a perfect example of how you can utilize tables virtually inside of your DAX measures.

CROSSJOIN is a table function and what it essentially enables you to do is merge two different columns (or for that matter tables) into one master table that you can place inside a measure.

I’ve personally utilized CROSSJOIN in iterating functions but you can also use them inside a CALCULATE statement to adjust the filter context.

Certainly, by going through this tutorial, you will understand in a lot greater detail how this all works.

The next table function and what can also be termed as a virtual table especially if you use it in a DAX measure is the INTERSECT function.

This particular function allows us to compare two different tables within one function. What we can do within this function is check to see if something exists in one table and also exists in the other table. Then ultimately create another table (virtually) which just contains the items where there was duplication.

It’s a really interesting function that can be used within different types of DAX measures to iterate through a certain grouping of your dimensions.

The last table function that I want to go through is called CALCULATETABLE.

CALCULATETABLE is a very interesting function and it works in a very similar way to CALCULATE. The big difference here is that CALCULATETABLE returns a table and not a specific result. This is really useful when you want to change the context of a table that you want to iterate through.

I’ve dived into this concept in this tutorial because it is sometimes difficult to get your mind around. If you have the time to review, I’m confident that you’ll enjoy the content and that you’ll have a light-bulb moment in terms of how you can utilize this function.

So we have 3 really detailed examples in this blog. I’ve included all these together because I thought that if you can understand all of these then you’ll have a very good grasp of what virtual tables are.

They are an essential topic and concept that you want to have a good understanding of when completing developing work and running analysis inside of Power BI.

All the best.

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