Maven Analytics – Data Drills #4 – Spot the Sale – DAX Query Solution

This is my DAX Query solution to Maven Analytics Data Drill #4 – Spot the Sale.

The challenge is to join each transaction to the promotion active on its order date, and find out how many orders were placed outside of the promotional period.

Disclaimer: This solution is based Aaron Perry’s DAX solution, an instructor from Maven Analytics.

For this challenge, my initial impulse was to unpivot the promotions table, so that the dates can all fall under one column. Once we have them under one column, then we can find a way to build a table that contains the correct promotion for each date, then perform a left join on the orders table.

Suggested Solution Example:

Promotions table

Orders Table after left join

However, I feel, the challenge involves how to accurately and dynamically expand the promotions table for each promotion date.

This approach, although may work, is still an SQL way of thinking. The better approach is to think in DAX. The better way is to create a new column that would check for each row, in the column if it belongs in the promotion range, then return the promotion name.

DEFINE
    COLUMN 'orders'[promo_ids] = 
        VAR d = 'orders'[order_date]
    RETURN
        CALCULATE(
            MAX('promotions'[promo_id]),
            FILTER(
                ALL('promotions'),
                'promotions'[start_date] <= d &&
                d <= 'promotions'[end_date]
            )
        )
EVALUATE
{
    COUNTROWS(
        FILTER( 'orders', ISBLANK( 'orders'[promo_ids] ) )
    )
}

DEFINE
    COLUMN 'orders'[promo_ids] = 
        VAR d = 'orders'[order_date]
    RETURN
        CALCULATE(
            MAX('promotions'[promo_id]),
            FILTER(
                ALL('promotions'),
                'promotions'[start_date] <= d &&
                d <= 'promotions'[end_date]
            )
        )
EVALUATE
{
    COUNTROWS(
        FILTER( 'orders', ISBLANK( 'orders'[promo_ids] ) )
    )
}

First, the current date being evaluated , in the row context and then saved in the variable d. We then remove all of the filters from the promotions table using ALL(), then explicitly filter the promotions table again, based on the condition that the order date is between the start date and end date. once this is true it returns a one row table (since there is no overlap in promotions). Thereafter we get the individual value using max and wrap it around calculate, in order to force the filter.

Once we have the column, we then query the orders table for blank promo ids.

Video Walkthrough

That’s all for today! 👋🏾😁