# How to use “CrossJoin” in DAX

The “CrossJoin” function returns the Cartesian product of all rows from all tables in the arguments. In my sample below I want to have sales per year per city.

```EVALUATE
SUMMARIZE (
CROSSJOIN (
VALUES ( 'Date'[Calendar Year] ),
VALUES ( Geography[City] )
),
'Date'[Calendar Year],
Geography[City]
),
"Sum of Sales", CALCULATE ( SUM ( 'Internet Sales'[Sales Amount] ) )
)```

And the result looks like this

But as you can see I get a lot of empty rows. To remove these empty rows I add a filter to my query. I also moved my calculation outside the main query to make it more readable.

```DEFINE
MEASURE 'Internet Sales'[Sales] =
SUM ( 'Internet Sales'[Sales Amount] )
EVALUATE
FILTER (
SUMMARIZE (
CROSSJOIN (
VALUES ( 'Date'[Calendar Year] ),
VALUES ( Geography[City] )
),
'Date'[Calendar Year],
Geography[City]
),
'Internet Sales'[Sales] <> 0
),
"Sum of Sales", 'Internet Sales'[Sales]
)```

# Return cross product of two or more sets in MDX using “CrossJoin”

One of the functions I use almost all the time when I am writing MDX is the crossjoin function. This function returns the cross product of two or more sets in MDX.

Lets look at an example

```SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Date].[Calendar].[Calendar Year] ON 1
```

This code will give us sales per year. But what if we want sales per year per city? Lets introduce the crossjoin function!

```SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY CrossJoin([Date].[Calendar].[Calendar Year], [Customer].[City].[City])  ON 1
```

And that would gives ut the result we want

How about sales per contry per city per year? Then we will have to nest 2 crossjoins like this.

```SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY CrossJoin([Customer].[Country].[Country], CrossJoin([Customer].[City].[City], [Date].[Calendar].[Calendar Year]))  ON 1
```SELECT [Measures].[Internet Sales Amount] ON 0,