Skip to main content

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