DAX is Data Analysis Expression language developed by Microsoft to interact with data in various ways. It is a formula language consisting of functions, operations and expressions that return some value. Although DAX is used for optimization, but sometimes inefficient coding techniques result in expensive operations which are not memory efficient. As an example,

CROSSJOIN() allows you to build Cartesian product between two tables and brings all possible combinations from both tables.

To see how CROSSJOIN() works:

1. Click on the Modeling tab in the Ribbon.
2. Click on New Table.
3. Type the following expression in the formula bar and press Enter.

In this Expression:

• A new table containing the Cartesian product of Customer and Product table is calculated.

4. Click on the Data tab in the left Ribbon.
5. Click on Testing Cross Join (CT) in the Fields
6. Scroll down to the end of table.

CROSSJOIN() with simple tables returns 7.4M rows and becomes an expensive operation to perform. We can use VALUES() function with Cross join to get all possible combinations of customer city and product subcategory.

Solving the problem of Data Explosion:

CROSSJOIN() and VALUES() can be used together to solve the problem of data explosion mentioned above. The first table function that you will be using to calculate tables is VALUES().

The result of VALUES() depends on the input argument:

• When the input argument is a table, all the rows of the table are returned. Duplicate rows are preserved.
• When the input argument is a column, a single-column table is returned containing distinct values. Duplicates are removed in this case and only unique values are returned.

To learn more about VALUES() in DAX: Using VALUES() in Calculated Tables – Power BI Training Australia

To use VALUES() with CROSSJOIN() to get all possible combination of customer city and product subcategory:

7. Click on the Testing Cross Join (CT) tab in the Fields.

Table tools open.

8. Type the following expression in the formula bar and press Enter.

In this Expression:

• A new table containing the Cartesian product of distinct Customer cities and distinct Product Subcategories is calculated.

9. Click on the Data tab in the left Ribbon.
10. Click on Testing Cross Join (CT) in the Fields pan.
11. Click on the drop down next to City.
12. Click on Sort Descending.
13. Scroll down to the end of table.

Notice the reducion in the number of rows.

Conclusion:

CROSSJOIN() as the name implies produces a cartesian product of all the rows of the tables used in the argument. This results in a data explosion as the number of rows in the resultant table are a cartesian product of all the rows in all the tables used. This operationally intensive calculation can be simplified using a simple DAX function VALUES(). In this blog post, the problem resulting from using CROSSJOIN() alone is addressed with a step by step guide.

Are you looking for an instructor-led Power BI or Power Apps training in Australia? We provide classroom as well as online live trainings for the following courses:

• • Power BI Essentials
  • Power BI Advanaced
  • DAX Essentials
  • Power Apps Essentials