DAX Queries, Part 4

I was extremely pleased to see that there was a Crossjoin() function in DAX, if only because it meant that I wouldn’t have to rename my company. Let’s see how to use it…

The Crossjoin() function returns a table that returns the cross join of two table expressions. Here’s a very simple example:

evaluate(
crossjoin(
values(DimDate[CalendarYear])
, values(DimDate[FiscalYear])
)
)

In this case the two parameters for Crossjoin() return a table of distinct values from DimDate[CalendarYear] and DimDate[FiscalYear], and the table returned gives every combination of values from those two tables. From an MDX point of view, it’s interesting to note that we really do get every single combination: there’s no auto-exists being applied, and we get combinations like CalendarYear 2001 and FiscalYear 2004 that do not exist in the DimDate table (I have no problem with this – it’s what I’d expect to happen in DAX).

I can imagine using Crossjoin() in a number of different ways, although the most obvious scenario is in a query along with the Summarize() function, for example:

evaluate(
summarize(
crossjoin(
values(DimDate[CalendarYear])
, values(DimProductCategory[EnglishProductCategoryName])
)
, DimDate[CalendarYear]
, DimProductCategory[EnglishProductCategoryName]
, “Sum of Sales”
, sum(FactInternetSales[SalesAmount])
)
)

It’s worth comparing the query above with the output of the following query:

evaluate(
summarize(
FactInternetSales
, DimDate[CalendarYear]
, DimProductCategory[EnglishProductCategoryName]
, “Sum of Sales”
, sum(FactInternetSales[SalesAmount])
)
)

Notice how, in the first query, you get one row for every distinct combination of Year and Category whether there are any sales or not, whereas in the second query you only see the combinations where sales exist.

In part 5, I’ll look at how to use the Generate() and TopN() functions.