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.
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
No, there isn’t unfortunately
Thanks
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?
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 )
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?
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.
OK. I got that to work via the “add a table based on a data model tab and then right click on the table and Table->Edit Dax” trick.
The DAX queries using this method only seems to be able to connect to the one table. If you want to do a crossjoin to join columns from two tables you need to combine the two columns into one table 1st.
(happy to be shown that I’m wrong 🙂
Thanks for the help!
-Alex
http://stackoverflow.com/questions/25510224/how-do-you-copy-the-powerpivot-data-into-the-excel-workbook-as-a-table/25514749#25514749 (link in the answer)