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 and its query plan.

The CROSSJOIN function can be used to create a Cartesian product of two tables. A Cartesian product is a set of all possible combinations of rows from two or more tables.

The DAX code below will be used in this tutorial.

You can see that the CROSSJOIN function results in a combination of the product Brand and Color even if some of these combinations donâ€™t exist in the Fact table. This results in a table with a total of 176 rows.

In the Server Timings tab, the first query retrieves the Brand from the Products table, whereas the second query retrieves the Color.

In the Logical Query Plan, the first line returns the result in the form of a table. The second and third lines in the products table scan the color and brand, respectively.

Once the data is received, the Formula Engine iterates over the table and produces a combination of both columns. The CrossApply in Line 2 corresponds to the CROSSJOIN function.

Filter CROSSJOIN Results In DAX

You can use the FILTER function to filter the results of a CROSSJOIN. For example, you may use it to only show products that are Red.

However, remember that you canâ€™t place a CROSSJOIN function within a FILTER argument.

Now if you run this code and check the Server Timings tab, youâ€™ll see that the filter context doesn’t appear as a WHERE clause in the query.

The CROSSJOIN function can only be resolved by the Formula Engine. The code doesnâ€™t work since it comes in between FILTER and VALUES which are functions that can only be resolved by the Storage Engine. Thus, the CROSSJOIN caused the loop between these two functions to break.

Therefore, you need to place the FILTER function within the CROSSJOIN argument for the filter to work.

Now if you run this code and check the Server Timings tab, youâ€™ll see that the FILTER function is now represented by the WHERE clause.

In the Logical Query Plan, you can see that instead of the Filter Operator, the Filter VertiPaq is used.

This is because the new filter condition in the code can be pushed down to the Storage Engine. After the result is returned, the CROSSJOIN argument is then performed inside the Formula Engine.

The IN Operator

The IN operator can be used within the expression argument of the FILTER function to filter a table based on whether a value is contained within a list of values or within a table.

When you use the FILTER – IN combination, the DAX engine has to perform an IsEmpty filter which confirms whether the product color is Red or Black.

Add Total Sales Column In CROSSJOIN DAX Result

Other functions can also be used together with CROSSJOIN.

You can use the ADDCOLUMNS function to add another column in your table and then write the argument of the information you want to show.

In this example, letâ€™s add a Total Sales column.

When you run the code, the results will still return 176 rows. However, some combinations will have blank values in the Total Sales column. This is because these combinations donâ€™t have a corresponding row in the Sales table.

In the Server Timings tab, the code first selects the Brand and Color. It then sums up the product of the Quantity and Sales Net Price.

In this case, the DAX engine first retrieves all the combinations of brand and color that exist inside the Products table, along with those with a corresponding row in the Sales table. Then, it retrieves the brand and color columns separately.

In the Physical Query Plan, you can see two data caches; one for the brand, and another for the color. When you multiply their total records, you get 176 rows.

Then, a lookup is performed on those 176 rows to see if they have a corresponding value in the Sales table. You can see that only 111 rows have values. The other 65 rows will return blanks.

Moreover, if you add more columns in the CROSSJOIN argument, the result will also return more rows.

Query Plan & Server Timings In DAX Studio
DAX Function COLUMNSTATISTICS In Power BI
Extracting A Query Result In DAX Studio Using The Output Option

Conclusion

The DAX CROSSJOIN function generates a new table by combining rows from one table with the rows from another. This function is similar to the CROSS JOIN clause in SQL and can be used to create more complex queries in your data model.

It can generate a list of all possible value combinations from two or more tables. It can also be used to quickly create a new table with a large number of rows, which can be useful for data analysis, testing, or performance benchmarking.

Overall, the CROSSJOIN function is a handy tool in scenarios where you need to combine data from multiple tables or generate a list of all possible combinations.

All the best,

Enterprise DNA Experts

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

R Scripting For Power BI Using RStudio

In this blog, weâ€™re going to go through the basics of R scripting for Power BI using RStudio. The...

Simple Aggregations In Power BI

In this blog post, I'll touch on some simple aggregations in Power BI that you can use in your DAX...

Microsoft Report Builder: Steps In Creating A List

In this tutorial, you will learn how to create a list and page break in Microsoft Report Builder. A...

5 Strategies To Enhance Your Power BI DAX Skills

In this tutorial, youâ€™ll learn the general strategies to improve your DAX or M capabilities. This is an...

ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

Calculations In Power BI Using Measure Branching

Measure Branching is a technique in making calculations in Power BI. It's not something you'll hear...

Power BI Ranking In Hierarchical Form

Today, we will learn how to calculate Power BI ranking in a hierarchical form which is a little bit...

Understanding Power BI Aggregations

Now, let's delve deeper into one of the most important concepts in Power BI calculations â€” the...

Using Python In Power BI | Dataset And String Function

In this tutorial, we'll discuss how you can create and prepare Dataset and String Function using Python...

Announcing The Enterprise DNA Submit A Showcase Program

Today we are launching a brand-new program here at Enterprise DNA. We are looking to collaborate with...

Inventory Management Reports To Show Trends In Sales

This Enterprise DNA Power BI Showcase focuses on Inventory Management. You may watch the full video of...

Huff Gravity Model Analysis in Power BI

In this tutorial, we'll learn how to do a Huff Gravity Model analysis in Power BI. We can use this...