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
You can also use the IN operator instead of the equal sign in a FILTER argument.
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.
***** Related Links *****
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
