CROSSJOIN DAX Function (Table manipulation)
Returns a table that is a crossjoin of the specified tables.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| Table | Repeatable |
A table that will participate in the crossjoin. |
Return values
A table that contains the Cartesian product of all rows from all tables in the arguments.
Remarks
Column names from table arguments must all be different in all tables or an error is returned.
The total number of rows returned by CROSSJOIN() is equal to the product of the number of rows from all tables in the arguments; also, the total number of columns in the result table is the sum of the number of columns in all tables. For example, if TableA has rA rows and cA columns, and TableB has rB rows and cB columns, and TableC has rC rows and cC column; then, the resulting table has rA × rB × rC rows and cA + cB + cC columns.
» 2 related functions
Examples
-- CROSSJOIN performs the cartesian product of two tables,
-- generating all the possible combinations of table rows
EVALUATE
CALCULATETABLE (
ADDCOLUMNS (
CROSSJOIN (
VALUES ( 'Product'[Color] ),
VALUES ( 'Product'[Category] )
),
"#Prods", CALCULATE ( COUNTROWS ( 'Product' ) )
),
'Product'[Category] IN { "TV and Video", "Computers" }
)
ORDER BY [Category], [Color]
| Color | Category | #Prods |
|---|---|---|
| Black | Computers | 194 |
| Blue | Computers | 20 |
| Brown | Computers | 11 |
| Gold | Computers | 4 |
| Green | Computers | 15 |
| Grey | Computers | 70 |
| Orange | Computers | 2 |
| Pink | Computers | 4 |
| Red | Computers | 15 |
| Silver | Computers | 78 |
| White | Computers | 188 |
| Yellow | Computers | 5 |
| Black | TV and Video | 72 |
| Blue | TV and Video | (Blank) |
| Brown | TV and Video | 53 |
| Gold | TV and Video | (Blank) |
| Green | TV and Video | (Blank) |
| Grey | TV and Video | (Blank) |
| Orange | TV and Video | (Blank) |
| Pink | TV and Video | (Blank) |
| Red | TV and Video | (Blank) |
| Silver | TV and Video | 71 |
| White | TV and Video | 26 |
| Yellow | TV and Video | (Blank) |
-- CROSSJOIN is useful to find non-existing combinations
-- that might be interesting to further analyze.
DEFINE
VAR Top5Colors =
TOPN ( 5, VALUES ( 'Product'[Color] ), [Sales Amount] )
VAR CategoriesWithoutTop5Colors =
FILTER (
CROSSJOIN ( VALUES ( 'Product'[Category] ), Top5Colors ),
CALCULATE ( ISEMPTY ( 'Product' ) )
)
EVALUATE
CategoriesWithoutTop5Colors
EVALUATE
Top5Colors
| Category | Color |
|---|---|
| TV and Video | Blue |
| Cell phones | Blue |
| Audio | Grey |
| TV and Video | Grey |
| Color |
|---|
| Blue |
| Grey |
| White |
| Silver |
| Black |
Related articles
Learn more about CROSSJOIN in the following articles:
-
From SQL to DAX: Joining Tables
In SQL there are different types of JOIN, available for different purposes. This article shows the equivalent syntaxes supported in DAX and it was updated in May 2018. » Read more
-
When to use KEEPFILTERS over iterators
This article describes how to use KEEPFILTERS in DAX iterator functions to preserve arbitrarily shaped filters in context transition. » Read more
-
Specifying multiple filter conditions in CALCULATE
This article introduces the new DAX syntax (March 2021) to support CALCULATE filter predicates that reference multiple columns from the same table. » Read more
Related functions
Other related functions are:
Last update: Nov 14, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/crossjoin-function-dax