Tag Archives: CrossJoin

How to use “CrossJoin” in DAX

Posted on by • Posted in Basic script • Tagged , • 1 Comment

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
ADDCOLUMNS (
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

CrossJoin1

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
ADDCOLUMNS (
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]
)

CrossJoin 1

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

Posted on by • Posted in MDX • Tagged ,

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
FROM [Adventure Works]

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
FROM [Adventure Works]ยจ

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
FROM [Adventure Works]

Instead of crossjoin you can also use a ‘*’. That is the way I ususally write my code

Here is an example on that

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Customer].[Country].[Country] * [Customer].[City].[City] * [Date].[Calendar].[Calendar Year]  ON 1
FROM [Adventure Works]