Chris Webb's BI Blog

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

DAX Queries, Part 4

with 9 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

9 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

  4. Hello Chris, Can you run the DAX query at the top (with the crossjoin() funtion) with-in Powerpivot against two existing tabs into a new tab in the data model?

    alex

    September 1, 2014 at 10:37 am

    • Yes, you should be able to – you would have to run the query, load it into a table in the worksheet, and then use the table in the worksheet as the data source for the third table.

      Chris Webb

      September 1, 2014 at 10:41 am


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,309 other followers

%d bloggers like this: