Questions , answers and Samples about row context transition in DAX

   Check list of all posts

Q1: What is row context transition?

Calculation transforms any existing row context into a filter context, or when the row value becomes a column's filter.

Q2: Why do we need a row context transition?

Because Row Context does NOT respect the relationship, In most cases we need to have a relationship to get data from different tables. even if we use a related and related table.

Q3: What triggers the row context transition?

We need to have two conditions: 1) have a row context; and 2) have CALCULATE

Q4: Where is row context involved?

There are two cases: 

Case 1: Calculated Columns

Fact sales table, you want to have a product subcategory, or product category, or any classifications

Dimension customer table, you want to have the sales amount

Case 2: All iterators

FILTER DAX Function (Aggregation)

SUMX DAX Function (Aggregation)

AVERAGEX DAX Function (Aggregation)

MAXX DAX Function (Aggregation)

MINX DAX Function (Aggregation)

PRODUCTX DAX Function (Aggregation)

COUNTAX DAX Function (Aggregation)

COUNTX DAX Function (Aggregation)

CONCATENATEX DAX Function (Text)

LASTNONBLANK DAX Function (Time Intelligence)  CONTEXT TRANSITION

FIRSTNONBLANK DAX Function (Time Intelligence)  CONTEXT TRANSITION

ADDCOLUMNS DAX Function (Table manipulation)

GENERATE DAX Function (Table manipulation)

GENERATEALL DAX Function (Table manipulation)

SELECTCOLUMNS DAX Function (Table manipulation)

SUBSTITUTEWITHINDEX DAX Function (Table manipulation)

RANKX DAX Function (Statistical)

GEOMEANX DAX Function (Statistical)

MEDIANX DAX Function (Statistical)

PERCENTILEX.EXC DAX Function (Statistical)

PERCENTILEX.INC DAX Function (Statistical)

SAMPLE DAX Function (Statistical)

STDEVX.P DAX Function (Statistical)

STDEVX.S DAX Function (Statistical)

Please note that the table/matrix visual row is not a row context.

Q5: What is an explicit row context transition?

CALCULATE is presented in DAX script.

Q6: What is an implicit row context transition?

Where you don't see CALCULATE, However, a measure is used, such as [SalesAmount]

Q7: What problems can happen when there are duplicated records?

especially for fact tables, such as sales, where sales records are not unique, we will get the wrong summary, as the filtered result is duplicated.

Q8: Any samples?

Correct - Explicit row context transition

DEFINE MEASURE Sales[Sales to big customers] =

SUMX (

    VALUES ( Customer[CustomerKey] ),

    VAR CustomerSales =

        CALCULATE (

            SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

        )

    VAR Result = IF ( CustomerSales >= 5000, CustomerSales )

    RETURN

        Result

)

EVALUATE

ADDCOLUMNS (

    VALUES ( 'Date'[Calendar Year Month] ),

    "SalesToBigCustomer", [Sales to big customers]

)

Wrong - No row context transition

DEFINE MEASURE Sales[Sales to big customers] =

SUMX (

    VALUES ( Customer[CustomerKey] ),

    VAR CustomerSales =

             SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

    VAR Result = IF ( CustomerSales >= 5000, CustomerSales )

    RETURN

        Result

)

EVALUATE

ADDCOLUMNS (

    VALUES ( 'Date'[Calendar Year Month] ),

    "SalesToBigCustomer", [Sales to big customers]

)

Correct -  Implicit row context transition

DEFINE MEASURE Sales[Sales to big customers] =

SUMX (

    VALUES ( Customer[CustomerKey] ),

  VAR CustomerSales = [Sales Amount]

    VAR Result = IF ( CustomerSales >= 5000, CustomerSales )

    RETURN

        Result

)

EVALUATE

ADDCOLUMNS (

    VALUES ( 'Date'[Calendar Year Month] ),

    "SalesToBigCustomer", [Sales to big customers]

)

Q9: Any DAX script Samples?

[Measure1] =
        CALCULATE (
            COUNTROWS (
                FILTER (
                    Customer,
                    CALCULATE (
                        ISEMPTY ( Sales )
                    )
                )
            ),
            Customer[Total Children] > 0,
            'Product'[Category] = "Games and Toys"
        )

[Measure2] =
        VAR CustomersWithTwoProducts =
            FILTER (
                Customer,
                CALCULATE (
                    DISTINCTCOUNT ( Sales[ProductKey] )
                ) >= 2
            )
        VAR Result =
            COUNTROWS ( CustomersWithTwoProducts )
        RETURN
            Result

[Measure3] =
        VAR NumProductsSelection =
            SELECTEDVALUE (
                'Num of Products'[Num Products],
                2
            )
        VAR CustomersWithSelectedProducts =
            FILTER (
                Customer,
                CALCULATE (
                    DISTINCTCOUNT ( Sales[ProductKey] )
                ) >= NumProductsSelection
            )
        VAR Result =
            COUNTROWS ( CustomersWithSelectedProducts )
        RETURN
            Result

[Measure4] =
        VAR Threshold = [Threshold]
        VAR MaxSpikes =
            FILTER (
                VALUES ( 'Date'[Year Month] ),
                VAR SalesCurrentMonth = [Sales Amount]
                VAR SalesPreviousMonth =
                    CALCULATE (
                        [Sales Amount],
                        PREVIOUSMONTH ( 'Date'[Date] )
                    )
                VAR EnteringThreshold = SalesCurrentMonth >= Threshold
                    && SalesPreviousMonth < Threshold
                RETURN
                    EnteringThreshold
            )
        VAR Result =
            COUNTROWS ( MaxSpikes )
        RETURN
            Result

[Measure5] =
        IF (
            ISINSCOPE ( Customer[Name] ),
            RANKX (
                ALLSELECTED ( Customer ),
                [Margin]
            )
        )


[Measure6] =
        SUMX (
            VALUES ( Balances[Name] ),
            [Last Bal NB]
        )

[Measure7] =
IF (
    HASONEVALUE ( 'Target Currency'[Currency] ),
    VAR AggregatedSalesInCurrency =
        ADDCOLUMNS (
            SUMMARIZE (
                Sales,
                'Date'[Calendar Year Month],
                'Source Currency'[Currency]
            ),
            "@SalesAmount", [Sales (Internal)],
            "@Rate",
                CALCULATE (
                    SELECTEDVALUE ( 'Monthly Exchange Rates'[Rate] )
                )
        )
    VAR Result =
        SUMX (
            AggregatedSalesInCurrency,
            IF (
                NOT (
                    ISBLANK ( [@Rate] )
                ),
                [@SalesAmount] * [@Rate],
                ERROR ( "Missing conversion rate" )
            )
        )
    RETURN
        Result
)


[Measure8] =
IF (
    HASONEVALUE ( 'Date'[Calendar Year] ),
    VAR CustomersInSegment =
        FILTER (
            ALLSELECTED ( Customer ),
            VAR SalesOfCustomer = [Sales Amount]
            VAR SegmentForCustomer =
                FILTER (
                    'Customer Segments',
                    NOT ISBLANK ( SalesOfCustomer )
                        && 'Customer Segments'[Min Sales] < SalesOfCustomer
                        && 'Customer Segments'[Max Sales] >= SalesOfCustomer
                )
            VAR IsCustomerInSegments =
                NOT ISEMPTY ( SegmentForCustomer )
            RETURN
                IsCustomerInSegments
        )
    VAR Result =
        CALCULATE (
            COUNTROWS ( Customer ),
            KEEPFILTERS ( CustomersInSegment )
        )
    RETURN
        Result
)