Skip to main content
Version: I2024.2.x

Crossjoin

Returns a set that is the cross product of one or more sets. Used to combine members from multiple hierarchies into a set of tuples for the purpose of constraining the evaluation of a numeric expression before aggregation.

Syntax

Crossjoin(Set_Expression1 ,Set_Expression2 [,...n] )  

Notes

  • The Crossjoin function does not support Role-Played dimensions.
  • If using Crossjoin Calculated Measures with Microsoft Power BI, AtScale recommends setting the Engine (or inbound Connection) to use the "Legacy Dax" dialect. This is the default system value.

Input Parameters

Set_Expression1

Required. A set expression produced by the DESCENDANTS function.

Set_ExpressionN

Optional. A set expression produced by the DESCENDANTS function.

Return Value

Returns a Set of Members, however intended to be used within an in-line MDX aggregation function.

Example

The following example performs Currency Conversion on source transaction amount [Measures].[Internet Sales Amount Local] by multiplying it by the conversion rate [Measures].[Average Day Rate] for each tuple defined by the Crossjoin of: [DateCustom].[Retail445].[Reporting Day], [CurrencyDim].[CurrencyHierarchy].[CurrencyLevel], and [Internet Sales Order Details].[Internet Sales Orders].[Order Number]. The resulting values are aggregated with the SUM function.

SUM(
CrossJoin(
Descendants(
[DateCustom].[Retail445].CurrentMember,
[DateCustom].[Retail445].[Reporting Day],
SELF
),
Descendants(
[CurrencyDim].[CurrencyHierarchy].CurrentMember,
[CurrencyDim].[CurrencyHierarchy].[CurrencyLevel],
SELF
),
Descendants(
[Internet Sales Order Details].[Internet Sales Orders].CurrentMember,
c,
SELF
)
),
[Measures].[Internet Sales Amount Local] * [Measures].[Average Day Rate]
)