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