Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

DAX Queries, Part 4

with 6 comments

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

image

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

image

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

image

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.

Written by Chris Webb

July 27, 2011 at 10:17 pm

Posted in DAX

6 Responses

Subscribe to comments with RSS.

  1. [...] part 4, I’ll take a look at the Crossjoin() [...]

  2. Hello Chris, is there any way to Pivot the results of the CrossJoin function, something like the PIVOT Function in SQL Queries , so far I have been forced to do it on either Excel or SSRS but I have a feeling that if done through DAX the performance will be much better

    Omar Sultan

    July 6, 2013 at 11:34 am

  3. Hey. So PowerPivot allows populating a columns using DAX, but CROSSJOIN returns a table. Can I populate a PowerPivot table from that? I wanna reuse the result table (so not storing in an intermediate table seems inefficient), but the result table exceeds 1M rows, meaning Excel sheets (and therefore DAX Studio) are out too. Is this possible?

    tycho01

    October 30, 2013 at 5:20 pm

    • No – this is something I’ve wanted to do myself but I don’t think it’s possible ( although you can do it with SSAS Tabular models )

      Chris Webb

      October 30, 2013 at 7:31 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,081 other followers

%d bloggers like this: