AdventureWorks query timelines

December 2013 visual refresh · One measured run per design · Engine timings

14 DAX queries - exact Power BI DAX

Engine wall 725.2 ms
Statement CPU 749 ms

0200400600800 ms
  • Month
  • Empty
  • Sales
  • Partial
  • Orders
  • AOV
  • SalesCompare
  • Margin
  • OrdersCompare
  • Profit
  • YoYCompare
  • 12-month trend
  • Country chart
  • Category chart

8 DAX queries

Engine wall 449.2 ms
Statement CPU 626 ms

0200400600800 ms
  • Month + Partial + Empty
  • Orders + AOV
  • SalesCompare + YoYCompare
  • Sales + Margin + Profit
  • OrdersCompare
  • 12-month trend
  • Country chart
  • Category chart

4 DAX queries - one-shot app

Engine wall 237.5 ms
Statement CPU 312 ms

0200400600800 ms
  • 12-month trend
  • Category chart
  • Country chart
  • KPI cards

2 DAX queries

Engine wall 246.3 ms
Statement CPU 188 ms

0200400600800 ms
  • Current KPIs + three charts
  • Historical comparisons

1 DAX query

Engine wall 453.3 ms
Statement CPU 453 ms

0200400600800 ms
  • All 11 cards + three charts

14-query plan: Month

Formats the selected month for the report heading.

Connector output: 1 measured row × 1 column in one returned table.

Returned columns: [AW Selected Month]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Selected Month] =
        (
            /* USER DAX BEGIN */
            VAR ym =
                SELECTEDVALUE ( DimDate[YearMonthKey] )
            RETURN
                IF (
                    ISBLANK ( ym ),
                    "Select one month",
                    FORMAT ( DATE ( INT ( ym / 100 ), MOD ( ym, 100 ), 1 ), "MMM yyyy" )
                ) /* USER DAX END */
        )
    VAR __DS0FilterTable =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )

EVALUATE
SUMMARIZECOLUMNS (
    __DS0FilterTable,
    "AW Selected Month", IGNORE ( 'FactInternetSales'[AW Selected Month] )
)

14-query plan: Empty

Returns a no-sales message when the current selection has no order lines.

Connector output: 1 measured row × 1 column in one returned table.

Returned columns: [AW Empty Text]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Empty Text] =
        (
            /* USER DAX BEGIN */
            IF (
                COALESCE ( [Order Lines], 0 ) = 0,
                "No sales for this selection",
                ""
            ) /* USER DAX END */
        )
    VAR __DS0FilterTable =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )

EVALUATE
SUMMARIZECOLUMNS (
    __DS0FilterTable,
    "AW Empty Text", IGNORE ( 'FactInternetSales'[AW Empty Text] )
)

14-query plan: Sales

Formats total sales for the selected month as a currency card.

Connector output: 1 measured row × 1 column in one returned table.

Returned columns: [AW Card Sales]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Card Sales] =
        (
            /* USER DAX BEGIN */
            VAR v = [Total Sales]
            RETURN
                IF (
                    ISBLANK ( v ),
                    "—",
                    IF (
                        ABS ( v ) >= 1000000,
                        FORMAT ( v / 1000000, "$0.0" ) & "M",
                        IF (
                            ABS ( v ) >= 1000,
                            FORMAT ( v / 1000, "$0.0" ) & "K",
                            FORMAT ( v, "$0.00" )
                        )
                    )
                ) /* USER DAX END */
        )
    VAR __DS0FilterTable =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )

EVALUATE
SUMMARIZECOLUMNS (
    __DS0FilterTable,
    "AW Card Sales", IGNORE ( 'FactInternetSales'[AW Card Sales] )
)

14-query plan: Partial

Returns the partial-month coverage badge when the selected month is incomplete.

Connector output: 1 measured row × 1 column in one returned table.

Returned columns: [AW Partial Badge]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Partial Badge] =
        ( /* USER DAX BEGIN */
        COALESCE ( [Partial Month Text], "" ) /* USER DAX END */
        )
    VAR __DS0FilterTable =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )

EVALUATE
SUMMARIZECOLUMNS (
    __DS0FilterTable,
    "AW Partial Badge", IGNORE ( 'FactInternetSales'[AW Partial Badge] )
)

14-query plan: Orders

Formats the selected month's distinct order count for the orders card.

Connector output: 1 measured row × 1 column in one returned table.

Returned columns: [AW Card Orders]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Card Orders] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Order Count] ),
                "—",
                FORMAT ( [Order Count], "#,0" )
            ) /* USER DAX END */
        )
    VAR __DS0FilterTable =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )

EVALUATE
SUMMARIZECOLUMNS (
    __DS0FilterTable,
    "AW Card Orders", IGNORE ( 'FactInternetSales'[AW Card Orders] )
)

14-query plan: AOV

Formats average order value for the selected month as currency.

Connector output: 1 measured row × 1 column in one returned table.

Returned columns: [AW Card AOV]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Card AOV] =
        (
            /* USER DAX BEGIN */
            IF ( ISBLANK ( [AOV] ), "—", FORMAT ( [AOV], "$#,0.00" ) ) /* USER DAX END */
        )
    VAR __DS0FilterTable =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )

EVALUATE
SUMMARIZECOLUMNS (
    __DS0FilterTable,
    "AW Card AOV", IGNORE ( 'FactInternetSales'[AW Card AOV] )
)

14-query plan: SalesCompare

Returns the formatted sales change from the prior month or explains why a comparison is unavailable.

Connector output: 1 measured row × 1 column in one returned table.

Returned columns: [AW Sales Compare]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Sales Compare] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Sales MoM %] ),
                [Sales Compare Text],
                IF ( [Sales MoM %] >= 0, "▲ ", "▼ " ) & [Sales Compare Text]
            ) /* USER DAX END */
        )
    VAR __DS0FilterTable =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )

EVALUATE
SUMMARIZECOLUMNS (
    __DS0FilterTable,
    "AW Sales Compare", IGNORE ( 'FactInternetSales'[AW Sales Compare] )
)

14-query plan: Margin

Formats gross margin for the selected month as a percentage.

Connector output: 1 measured row × 1 column in one returned table.

Returned columns: [AW Card Margin]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Card Margin] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Gross Margin] ),
                "—",
                FORMAT ( [Gross Margin], "0.0%" )
            ) /* USER DAX END */
        )
    VAR __DS0FilterTable =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )

EVALUATE
SUMMARIZECOLUMNS (
    __DS0FilterTable,
    "AW Card Margin", IGNORE ( 'FactInternetSales'[AW Card Margin] )
)

14-query plan: OrdersCompare

Returns the formatted order-count change from the prior month or explains why a comparison is unavailable.

Connector output: 1 measured row × 1 column in one returned table.

Returned columns: [AW Orders Compare]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Orders Compare] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Orders MoM %] ),
                [Orders Compare Text],
                IF ( [Orders MoM %] >= 0, "▲ ", "▼ " ) & [Orders Compare Text]
            ) /* USER DAX END */
        )
    VAR __DS0FilterTable =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )

EVALUATE
SUMMARIZECOLUMNS (
    __DS0FilterTable,
    "AW Orders Compare", IGNORE ( 'FactInternetSales'[AW Orders Compare] )
)

14-query plan: Profit

Returns numeric gross profit for the selected month.

Connector output: 1 measured row × 1 column in one returned table.

Returned columns: [Gross_Profit]

Executed DAX (formatted by SQLBI)

DEFINE
    VAR __DS0FilterTable =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )

EVALUATE
SUMMARIZECOLUMNS (
    __DS0FilterTable,
    "Gross_Profit", IGNORE ( 'FactInternetSales'[Gross Profit] )
)

14-query plan: YoYCompare

Returns the formatted sales change from the same month last year or explains why a comparison is unavailable.

Connector output: 1 measured row × 1 column in one returned table.

Returned columns: [AW YoY Compare]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW YoY Compare] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Sales YoY %] ),
                [Sales YoY Compare Text],
                IF ( [Sales YoY %] >= 0, "▲ ", "▼ " ) & [Sales YoY Compare Text]
            ) /* USER DAX END */
        )
    VAR __DS0FilterTable =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )

EVALUATE
SUMMARIZECOLUMNS (
    __DS0FilterTable,
    "AW YoY Compare", IGNORE ( 'FactInternetSales'[AW YoY Compare] )
)

14-query plan: 12-month trend

Returns the native 12-month trend title and its data rows in two separate result tables.

Connector output: 1 measured row × 1 column in result 1 of 2; result 2 (chart body) is not exposed by the connector.

Returned columns: [AW Trend Title]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Trend Title] =
        (
            /* USER DAX BEGIN */
            VAR hi = [Source Last Date]
            VAR lo =
                EDATE ( DATE ( YEAR ( hi ), MONTH ( hi ), 1 ), -11 )
            VAR n =
                CALCULATE (
                    [Order Lines],
                    FILTER (
                        ALL ( DimDate ),
                        DimDate[FullDateAlternateKey] >= lo
                            && DimDate[FullDateAlternateKey] <= EOMONTH ( hi, 0 )
                    )
                )
            VAR coverage =
                CALCULATE (
                    [Partial Month Text],
                    REMOVEFILTERS ( DimDate ),
                    TREATAS ( { FORMAT ( hi, "yyyy-MM" ) }, DimDate[YearMonth] )
                )
            RETURN
                "12-month trend · " & FORMAT ( lo, "MMM yyyy" ) & "–"
                    & FORMAT ( hi, "MMM yyyy" )
                    & IF (
                        COALESCE ( n, 0 ) = 0,
                        " · No data for the current filters — clear Where/What or Reset",
                        ""
                    )
                    & IF ( NOT ISBLANK ( coverage ), " · " & coverage, "" ) /* USER DAX END */
        )
    VAR __ValueFilterDM0 =
        FILTER (
            KEEPFILTERS (
                SUMMARIZECOLUMNS (
                    'DimDate'[YearMonth],
                    'DimDate'[YearMonthKey],
                    "Trend_Window", IGNORE ( 'FactInternetSales'[Trend Window] )
                )
            ),
            [Trend_Window] = 1
        )
    VAR __DS0Core =
        SUMMARIZECOLUMNS (
            'DimDate'[YearMonth],
            'DimDate'[YearMonthKey],
            __ValueFilterDM0,
            "Total_Sales", 'FactInternetSales'[Total Sales],
            "Order_Count", IGNORE ( 'FactInternetSales'[Order Count] ),
            "Gross_Margin", IGNORE ( 'FactInternetSales'[Gross Margin] ),
            "Partial_Month_Text", IGNORE ( 'FactInternetSales'[Partial Month Text] )
        )
    VAR __DS0PrimaryShowAll =
        ADDMISSINGITEMS ( 'DimDate'[YearMonth],
        'DimDate'[YearMonthKey],
        __DS0Core,
        'DimDate'[YearMonth],
        'DimDate'[YearMonthKey],
        __ValueFilterDM0 )
    VAR __DS0PrimaryWindowed =
        TOPN (
            1001,
            __DS0PrimaryShowAll,
            'DimDate'[YearMonthKey], 1,
            'DimDate'[YearMonth], 1
        )

EVALUATE
SUMMARIZECOLUMNS (
    __ValueFilterDM0,
    "AW Trend Title", IGNORE ( 'FactInternetSales'[AW Trend Title] )
)

EVALUATE
__DS0PrimaryWindowed
ORDER BY
    'DimDate'[YearMonthKey],
    'DimDate'[YearMonth]

14-query plan: Country chart

Returns the native country chart title and its data rows in two separate result tables.

Connector output: 1 measured row × 1 column in result 1 of 2; result 2 (chart body) is not exposed by the connector.

Returned columns: [AW Country Title]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Country Title] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Total Sales] ),
                "No data for the current filters",
                "Where · Country — click to filter; right-click to drill through"
            ) /* USER DAX END */
        )
    VAR __DS0FilterTable =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )
    VAR __DS0Core =
        SUMMARIZECOLUMNS (
            'DimSalesTerritory'[SalesTerritoryCountry],
            __DS0FilterTable,
            "Total_Sales", 'FactInternetSales'[Total Sales],
            "Gross_Margin", IGNORE ( 'FactInternetSales'[Gross Margin] ),
            "Order_Count", IGNORE ( 'FactInternetSales'[Order Count] )
        )
    VAR __DS0PrimaryWindowed =
        TOPN (
            1001,
            __DS0Core,
            [Total_Sales], 0,
            'DimSalesTerritory'[SalesTerritoryCountry], 1
        )

EVALUATE
SUMMARIZECOLUMNS (
    __DS0FilterTable,
    "AW Country Title", IGNORE ( 'FactInternetSales'[AW Country Title] )
)

EVALUATE
__DS0PrimaryWindowed
ORDER BY
    [Total_Sales] DESC,
    'DimSalesTerritory'[SalesTerritoryCountry]

14-query plan: Category chart

Returns the native category chart title and its data rows in two separate result tables.

Connector output: 1 measured row × 1 column in result 1 of 2; result 2 (chart body) is not exposed by the connector.

Returned columns: [AW Category Title]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Category Title] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Total Sales] ),
                "No data for the current filters",
                "What · Category — click to filter; right-click to drill through"
            ) /* USER DAX END */
        )
    VAR __DS0FilterTable =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )
    VAR __DS0Core =
        SUMMARIZECOLUMNS (
            'DimProductCategory'[EnglishProductCategoryName],
            __DS0FilterTable,
            "Total_Sales", 'FactInternetSales'[Total Sales],
            "Gross_Margin", IGNORE ( 'FactInternetSales'[Gross Margin] ),
            "Order_Count", IGNORE ( 'FactInternetSales'[Order Count] )
        )
    VAR __DS0PrimaryWindowed =
        TOPN (
            1001,
            __DS0Core,
            [Total_Sales], 0,
            'DimProductCategory'[EnglishProductCategoryName], 1
        )

EVALUATE
SUMMARIZECOLUMNS (
    __DS0FilterTable,
    "AW Category Title", IGNORE ( 'FactInternetSales'[AW Category Title] )
)

EVALUATE
__DS0PrimaryWindowed
ORDER BY
    [Total_Sales] DESC,
    'DimProductCategory'[EnglishProductCategoryName]

8-query plan: Month + Partial + Empty

Returns the selected-month heading, partial-month badge, and no-sales message together.

Connector output: 1 measured row × 3 columns in one returned table.

Returned columns: [__card_Month], [__card_Partial], [__card_Empty]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Selected Month] =
        (
            /* USER DAX BEGIN */
            VAR ym =
                SELECTEDVALUE ( DimDate[YearMonthKey] )
            RETURN
                IF (
                    ISBLANK ( ym ),
                    "Select one month",
                    FORMAT ( DATE ( INT ( ym / 100 ), MOD ( ym, 100 ), 1 ), "MMM yyyy" )
                ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Partial Badge] =
        ( /* USER DAX BEGIN */
        COALESCE ( [Partial Month Text], "" ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Empty Text] =
        (
            /* USER DAX BEGIN */
            IF (
                COALESCE ( [Order Lines], 0 ) = 0,
                "No sales for this selection",
                ""
            ) /* USER DAX END */
        )
    VAR __CardsFilter =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )

EVALUATE
SUMMARIZECOLUMNS (
    __CardsFilter,
    "__card_Month", IGNORE ( 'FactInternetSales'[AW Selected Month] ),
    "__card_Partial", IGNORE ( 'FactInternetSales'[AW Partial Badge] ),
    "__card_Empty", IGNORE ( 'FactInternetSales'[AW Empty Text] )
)

8-query plan: Orders + AOV

Returns the selected month's formatted order count and average order value together.

Connector output: 1 measured row × 2 columns in one returned table.

Returned columns: [__card_Orders], [__card_AOV]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Card Orders] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Order Count] ),
                "—",
                FORMAT ( [Order Count], "#,0" )
            ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Card AOV] =
        (
            /* USER DAX BEGIN */
            IF ( ISBLANK ( [AOV] ), "—", FORMAT ( [AOV], "$#,0.00" ) ) /* USER DAX END */
        )
    VAR __CardsFilter =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )

EVALUATE
SUMMARIZECOLUMNS (
    __CardsFilter,
    "__card_Orders", IGNORE ( 'FactInternetSales'[AW Card Orders] ),
    "__card_AOV", IGNORE ( 'FactInternetSales'[AW Card AOV] )
)

8-query plan: SalesCompare + YoYCompare

Returns the month-over-month and year-over-year sales comparisons together.

Connector output: 1 measured row × 2 columns in one returned table.

Returned columns: [__card_SalesCompare], [__card_YoYCompare]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Sales Compare] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Sales MoM %] ),
                [Sales Compare Text],
                IF ( [Sales MoM %] >= 0, "▲ ", "▼ " ) & [Sales Compare Text]
            ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW YoY Compare] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Sales YoY %] ),
                [Sales YoY Compare Text],
                IF ( [Sales YoY %] >= 0, "▲ ", "▼ " ) & [Sales YoY Compare Text]
            ) /* USER DAX END */
        )
    VAR __CardsFilter =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )

EVALUATE
SUMMARIZECOLUMNS (
    __CardsFilter,
    "__card_SalesCompare", IGNORE ( 'FactInternetSales'[AW Sales Compare] ),
    "__card_YoYCompare", IGNORE ( 'FactInternetSales'[AW YoY Compare] )
)

8-query plan: Sales + Margin + Profit

Returns formatted sales and gross margin alongside numeric gross profit for the selected month.

Connector output: 1 measured row × 3 columns in one returned table.

Returned columns: [__card_Sales], [__card_Margin], [__card_Profit]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Card Sales] =
        (
            /* USER DAX BEGIN */
            VAR v = [Total Sales]
            RETURN
                IF (
                    ISBLANK ( v ),
                    "—",
                    IF (
                        ABS ( v ) >= 1000000,
                        FORMAT ( v / 1000000, "$0.0" ) & "M",
                        IF (
                            ABS ( v ) >= 1000,
                            FORMAT ( v / 1000, "$0.0" ) & "K",
                            FORMAT ( v, "$0.00" )
                        )
                    )
                ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Card Margin] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Gross Margin] ),
                "—",
                FORMAT ( [Gross Margin], "0.0%" )
            ) /* USER DAX END */
        )
    VAR __CardsFilter =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )

EVALUATE
SUMMARIZECOLUMNS (
    __CardsFilter,
    "__card_Sales", IGNORE ( 'FactInternetSales'[AW Card Sales] ),
    "__card_Margin", IGNORE ( 'FactInternetSales'[AW Card Margin] ),
    "__card_Profit", IGNORE ( 'FactInternetSales'[Gross Profit] )
)

8-query plan: OrdersCompare

Returns the formatted order-count change from the prior month or explains why a comparison is unavailable.

Connector output: 1 measured row × 1 column in one returned table.

Returned columns: [AW Orders Compare]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Orders Compare] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Orders MoM %] ),
                [Orders Compare Text],
                IF ( [Orders MoM %] >= 0, "▲ ", "▼ " ) & [Orders Compare Text]
            ) /* USER DAX END */
        )
    VAR __DS0FilterTable =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )

EVALUATE
SUMMARIZECOLUMNS (
    __DS0FilterTable,
    "AW Orders Compare", IGNORE ( 'FactInternetSales'[AW Orders Compare] )
)

8-query plan: 12-month trend

Returns the trend title and a twelve-month series of sales, orders, margin, and coverage notes in one table.

Connector output: 13 measured rows × 11 columns in one returned table.

Returned columns: [__slot], [__part], [__label], [__date], [__sales], [__margin], [__orders], [__partial], [__text], [__num], [__count]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Trend Title] =
        (
            /* USER DAX BEGIN */
            VAR hi = [Source Last Date]
            VAR lo =
                EDATE ( DATE ( YEAR ( hi ), MONTH ( hi ), 1 ), -11 )
            VAR n =
                CALCULATE (
                    [Order Lines],
                    FILTER (
                        ALL ( DimDate ),
                        DimDate[FullDateAlternateKey] >= lo
                            && DimDate[FullDateAlternateKey] <= EOMONTH ( hi, 0 )
                    )
                )
            VAR coverage =
                CALCULATE (
                    [Partial Month Text],
                    REMOVEFILTERS ( DimDate ),
                    TREATAS ( { FORMAT ( hi, "yyyy-MM" ) }, DimDate[YearMonth] )
                )
            RETURN
                "12-month trend · " & FORMAT ( lo, "MMM yyyy" ) & "–"
                    & FORMAT ( hi, "MMM yyyy" )
                    & IF (
                        COALESCE ( n, 0 ) = 0,
                        " · No data for the current filters — clear Where/What or Reset",
                        ""
                    )
                    & IF ( NOT ISBLANK ( coverage ), " · " & coverage, "" ) /* USER DAX END */
        )
    VAR __ValueFilterDM0_trend =
        FILTER (
            KEEPFILTERS (
                SUMMARIZECOLUMNS (
                    'DimDate'[YearMonth],
                    'DimDate'[YearMonthKey],
                    "Trend_Window", IGNORE ( 'FactInternetSales'[Trend Window] )
                )
            ),
            [Trend_Window] = 1
        )
    VAR __DS0Core_trend =
        SUMMARIZECOLUMNS (
            'DimDate'[YearMonth],
            'DimDate'[YearMonthKey],
            __ValueFilterDM0_trend,
            "Total_Sales", 'FactInternetSales'[Total Sales],
            "Order_Count", IGNORE ( 'FactInternetSales'[Order Count] ),
            "Gross_Margin", IGNORE ( 'FactInternetSales'[Gross Margin] ),
            "Partial_Month_Text", IGNORE ( 'FactInternetSales'[Partial Month Text] )
        )
    VAR __DS0PrimaryShowAll_trend =
        ADDMISSINGITEMS ( 'DimDate'[YearMonth],
        'DimDate'[YearMonthKey],
        __DS0Core_trend,
        'DimDate'[YearMonth],
        'DimDate'[YearMonthKey],
        __ValueFilterDM0_trend )
    VAR __DS0PrimaryWindowed_trend =
        TOPN (
            1001,
            __DS0PrimaryShowAll_trend,
            'DimDate'[YearMonthKey], 1,
            'DimDate'[YearMonth], 1
        )

EVALUATE
UNION (
    SELECTCOLUMNS (
        SUMMARIZECOLUMNS (
            __ValueFilterDM0_trend,
            "AW Trend Title", IGNORE ( 'FactInternetSales'[AW Trend Title] )
        ),
        "__slot", "trend",
        "__part", 0,
        "__label", BLANK (),
        "__date", BLANK (),
        "__sales", BLANK (),
        "__margin", BLANK (),
        "__orders", BLANK (),
        "__partial", BLANK (),
        "__text", [AW Trend Title],
        "__num", BLANK (),
        "__count", COALESCE ( COUNTROWS ( __DS0PrimaryWindowed_trend ), 0 )
    ),
    SELECTCOLUMNS (
        __DS0PrimaryWindowed_trend,
        "__slot", "trend",
        "__part", 1,
        "__label", 'DimDate'[YearMonth],
        "__date", 'DimDate'[YearMonthKey],
        "__sales", [Total_Sales],
        "__margin", [Gross_Margin],
        "__orders", [Order_Count],
        "__partial", [Partial_Month_Text],
        "__text", BLANK (),
        "__num", BLANK (),
        "__count", BLANK ()
    )
)
ORDER BY
    [__slot],
    [__part],
    [__date],
    [__sales] DESC,
    [__label]

8-query plan: Country chart

Returns the country chart title and country-level sales, margin, and orders, ranked by sales.

Connector output: 7 measured rows × 11 columns in one returned table.

Returned columns: [__slot], [__part], [__label], [__date], [__sales], [__margin], [__orders], [__partial], [__text], [__num], [__count]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Country Title] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Total Sales] ),
                "No data for the current filters",
                "Where · Country — click to filter; right-click to drill through"
            ) /* USER DAX END */
        )
    VAR __DS0FilterTable_countries =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )
    VAR __DS0Core_countries =
        SUMMARIZECOLUMNS (
            'DimSalesTerritory'[SalesTerritoryCountry],
            __DS0FilterTable_countries,
            "Total_Sales", 'FactInternetSales'[Total Sales],
            "Gross_Margin", IGNORE ( 'FactInternetSales'[Gross Margin] ),
            "Order_Count", IGNORE ( 'FactInternetSales'[Order Count] )
        )
    VAR __DS0PrimaryWindowed_countries =
        TOPN (
            1001,
            __DS0Core_countries,
            [Total_Sales], 0,
            'DimSalesTerritory'[SalesTerritoryCountry], 1
        )

EVALUATE
UNION (
    SELECTCOLUMNS (
        SUMMARIZECOLUMNS (
            __DS0FilterTable_countries,
            "AW Country Title", IGNORE ( 'FactInternetSales'[AW Country Title] )
        ),
        "__slot", "countries",
        "__part", 0,
        "__label", BLANK (),
        "__date", BLANK (),
        "__sales", BLANK (),
        "__margin", BLANK (),
        "__orders", BLANK (),
        "__partial", BLANK (),
        "__text", [AW Country Title],
        "__num", BLANK (),
        "__count", COALESCE ( COUNTROWS ( __DS0PrimaryWindowed_countries ), 0 )
    ),
    SELECTCOLUMNS (
        __DS0PrimaryWindowed_countries,
        "__slot", "countries",
        "__part", 1,
        "__label", 'DimSalesTerritory'[SalesTerritoryCountry],
        "__date", BLANK (),
        "__sales", [Total_Sales],
        "__margin", [Gross_Margin],
        "__orders", [Order_Count],
        "__partial", BLANK (),
        "__text", BLANK (),
        "__num", BLANK (),
        "__count", BLANK ()
    )
)
ORDER BY
    [__slot],
    [__part],
    [__date],
    [__sales] DESC,
    [__label]

8-query plan: Category chart

Returns the category chart title and category-level sales, margin, and orders, ranked by sales.

Connector output: 4 measured rows × 11 columns in one returned table.

Returned columns: [__slot], [__part], [__label], [__date], [__sales], [__margin], [__orders], [__partial], [__text], [__num], [__count]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Category Title] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Total Sales] ),
                "No data for the current filters",
                "What · Category — click to filter; right-click to drill through"
            ) /* USER DAX END */
        )
    VAR __DS0FilterTable_categories =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )
    VAR __DS0Core_categories =
        SUMMARIZECOLUMNS (
            'DimProductCategory'[EnglishProductCategoryName],
            __DS0FilterTable_categories,
            "Total_Sales", 'FactInternetSales'[Total Sales],
            "Gross_Margin", IGNORE ( 'FactInternetSales'[Gross Margin] ),
            "Order_Count", IGNORE ( 'FactInternetSales'[Order Count] )
        )
    VAR __DS0PrimaryWindowed_categories =
        TOPN (
            1001,
            __DS0Core_categories,
            [Total_Sales], 0,
            'DimProductCategory'[EnglishProductCategoryName], 1
        )

EVALUATE
UNION (
    SELECTCOLUMNS (
        SUMMARIZECOLUMNS (
            __DS0FilterTable_categories,
            "AW Category Title", IGNORE ( 'FactInternetSales'[AW Category Title] )
        ),
        "__slot", "categories",
        "__part", 0,
        "__label", BLANK (),
        "__date", BLANK (),
        "__sales", BLANK (),
        "__margin", BLANK (),
        "__orders", BLANK (),
        "__partial", BLANK (),
        "__text", [AW Category Title],
        "__num", BLANK (),
        "__count", COALESCE ( COUNTROWS ( __DS0PrimaryWindowed_categories ), 0 )
    ),
    SELECTCOLUMNS (
        __DS0PrimaryWindowed_categories,
        "__slot", "categories",
        "__part", 1,
        "__label", 'DimProductCategory'[EnglishProductCategoryName],
        "__date", BLANK (),
        "__sales", [Total_Sales],
        "__margin", [Gross_Margin],
        "__orders", [Order_Count],
        "__partial", BLANK (),
        "__text", BLANK (),
        "__num", BLANK (),
        "__count", BLANK ()
    )
)
ORDER BY
    [__slot],
    [__part],
    [__date],
    [__sales] DESC,
    [__label]

1-query plan: All 11 cards + three charts

Returns all 11 KPI cards, including the historical comparisons, plus the 12-month trend, country chart and category chart rows in one sparse result table.

Connector output: 25 measured rows × 22 columns in one returned table.

Returned columns: [__slot], [__part], [__label], [__date], [__sales], [__margin], [__orders], [__partial], [__text], [__num], [__count], [__card_Month], [__card_Partial], [__card_Empty], [__card_Sales], [__card_Margin], [__card_Orders], [__card_AOV], [__card_SalesCompare], [__card_Profit], [__card_OrdersCompare], [__card_YoYCompare]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Selected Month] =
        (
            /* USER DAX BEGIN */
            VAR ym =
                SELECTEDVALUE ( DimDate[YearMonthKey] )
            RETURN
                IF (
                    ISBLANK ( ym ),
                    "Select one month",
                    FORMAT ( DATE ( INT ( ym / 100 ), MOD ( ym, 100 ), 1 ), "MMM yyyy" )
                ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Partial Badge] =
        ( /* USER DAX BEGIN */
        COALESCE ( [Partial Month Text], "" ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Empty Text] =
        (
            /* USER DAX BEGIN */
            IF (
                COALESCE ( [Order Lines], 0 ) = 0,
                "No sales for this selection",
                ""
            ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Card Sales] =
        (
            /* USER DAX BEGIN */
            VAR v = [Total Sales]
            RETURN
                IF (
                    ISBLANK ( v ),
                    "—",
                    IF (
                        ABS ( v ) >= 1000000,
                        FORMAT ( v / 1000000, "$0.0" ) & "M",
                        IF (
                            ABS ( v ) >= 1000,
                            FORMAT ( v / 1000, "$0.0" ) & "K",
                            FORMAT ( v, "$0.00" )
                        )
                    )
                ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Card Margin] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Gross Margin] ),
                "—",
                FORMAT ( [Gross Margin], "0.0%" )
            ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Card Orders] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Order Count] ),
                "—",
                FORMAT ( [Order Count], "#,0" )
            ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Card AOV] =
        (
            /* USER DAX BEGIN */
            IF ( ISBLANK ( [AOV] ), "—", FORMAT ( [AOV], "$#,0.00" ) ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Sales Compare] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Sales MoM %] ),
                [Sales Compare Text],
                IF ( [Sales MoM %] >= 0, "▲ ", "▼ " ) & [Sales Compare Text]
            ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Orders Compare] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Orders MoM %] ),
                [Orders Compare Text],
                IF ( [Orders MoM %] >= 0, "▲ ", "▼ " ) & [Orders Compare Text]
            ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW YoY Compare] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Sales YoY %] ),
                [Sales YoY Compare Text],
                IF ( [Sales YoY %] >= 0, "▲ ", "▼ " ) & [Sales YoY Compare Text]
            ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Trend Title] =
        (
            /* USER DAX BEGIN */
            VAR hi = [Source Last Date]
            VAR lo =
                EDATE ( DATE ( YEAR ( hi ), MONTH ( hi ), 1 ), -11 )
            VAR n =
                CALCULATE (
                    [Order Lines],
                    FILTER (
                        ALL ( DimDate ),
                        DimDate[FullDateAlternateKey] >= lo
                            && DimDate[FullDateAlternateKey] <= EOMONTH ( hi, 0 )
                    )
                )
            VAR coverage =
                CALCULATE (
                    [Partial Month Text],
                    REMOVEFILTERS ( DimDate ),
                    TREATAS ( { FORMAT ( hi, "yyyy-MM" ) }, DimDate[YearMonth] )
                )
            RETURN
                "12-month trend · " & FORMAT ( lo, "MMM yyyy" ) & "–"
                    & FORMAT ( hi, "MMM yyyy" )
                    & IF (
                        COALESCE ( n, 0 ) = 0,
                        " · No data for the current filters — clear Where/What or Reset",
                        ""
                    )
                    & IF ( NOT ISBLANK ( coverage ), " · " & coverage, "" ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Country Title] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Total Sales] ),
                "No data for the current filters",
                "Where · Country — click to filter; right-click to drill through"
            ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Category Title] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Total Sales] ),
                "No data for the current filters",
                "What · Category — click to filter; right-click to drill through"
            ) /* USER DAX END */
        )
    VAR __CardsFilter =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )
    VAR __ValueFilterDM0_trend =
        FILTER (
            KEEPFILTERS (
                SUMMARIZECOLUMNS (
                    'DimDate'[YearMonth],
                    'DimDate'[YearMonthKey],
                    "Trend_Window", IGNORE ( 'FactInternetSales'[Trend Window] )
                )
            ),
            [Trend_Window] = 1
        )
    VAR __DS0Core_trend =
        SUMMARIZECOLUMNS (
            'DimDate'[YearMonth],
            'DimDate'[YearMonthKey],
            __ValueFilterDM0_trend,
            "Total_Sales", 'FactInternetSales'[Total Sales],
            "Order_Count", IGNORE ( 'FactInternetSales'[Order Count] ),
            "Gross_Margin", IGNORE ( 'FactInternetSales'[Gross Margin] ),
            "Partial_Month_Text", IGNORE ( 'FactInternetSales'[Partial Month Text] )
        )
    VAR __DS0PrimaryShowAll_trend =
        ADDMISSINGITEMS ( 'DimDate'[YearMonth],
        'DimDate'[YearMonthKey],
        __DS0Core_trend,
        'DimDate'[YearMonth],
        'DimDate'[YearMonthKey],
        __ValueFilterDM0_trend )
    VAR __DS0PrimaryWindowed_trend =
        TOPN (
            1001,
            __DS0PrimaryShowAll_trend,
            'DimDate'[YearMonthKey], 1,
            'DimDate'[YearMonth], 1
        )
    VAR __DS0FilterTable_countries =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )
    VAR __DS0Core_countries =
        SUMMARIZECOLUMNS (
            'DimSalesTerritory'[SalesTerritoryCountry],
            __DS0FilterTable_countries,
            "Total_Sales", 'FactInternetSales'[Total Sales],
            "Gross_Margin", IGNORE ( 'FactInternetSales'[Gross Margin] ),
            "Order_Count", IGNORE ( 'FactInternetSales'[Order Count] )
        )
    VAR __DS0PrimaryWindowed_countries =
        TOPN (
            1001,
            __DS0Core_countries,
            [Total_Sales], 0,
            'DimSalesTerritory'[SalesTerritoryCountry], 1
        )
    VAR __DS0FilterTable_categories =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )
    VAR __DS0Core_categories =
        SUMMARIZECOLUMNS (
            'DimProductCategory'[EnglishProductCategoryName],
            __DS0FilterTable_categories,
            "Total_Sales", 'FactInternetSales'[Total Sales],
            "Gross_Margin", IGNORE ( 'FactInternetSales'[Gross Margin] ),
            "Order_Count", IGNORE ( 'FactInternetSales'[Order Count] )
        )
    VAR __DS0PrimaryWindowed_categories =
        TOPN (
            1001,
            __DS0Core_categories,
            [Total_Sales], 0,
            'DimProductCategory'[EnglishProductCategoryName], 1
        )
    VAR __Wide =
        SUMMARIZECOLUMNS (
            __CardsFilter,
            "__card_Month", IGNORE ( 'FactInternetSales'[AW Selected Month] ),
            "__card_Partial", IGNORE ( 'FactInternetSales'[AW Partial Badge] ),
            "__card_Empty", IGNORE ( 'FactInternetSales'[AW Empty Text] ),
            "__card_Sales", IGNORE ( 'FactInternetSales'[AW Card Sales] ),
            "__card_Margin", IGNORE ( 'FactInternetSales'[AW Card Margin] ),
            "__card_Orders", IGNORE ( 'FactInternetSales'[AW Card Orders] ),
            "__card_AOV", IGNORE ( 'FactInternetSales'[AW Card AOV] ),
            "__card_SalesCompare", IGNORE ( 'FactInternetSales'[AW Sales Compare] ),
            "__card_Profit", IGNORE ( 'FactInternetSales'[Gross Profit] ),
            "__card_OrdersCompare", IGNORE ( 'FactInternetSales'[AW Orders Compare] ),
            "__card_YoYCompare", IGNORE ( 'FactInternetSales'[AW YoY Compare] )
        )

EVALUATE
UNION (
    SELECTCOLUMNS (
        __Wide,
        "__slot", "cards",
        "__part", 0,
        "__label", BLANK (),
        "__date", BLANK (),
        "__sales", BLANK (),
        "__margin", BLANK (),
        "__orders", BLANK (),
        "__partial", BLANK (),
        "__text", BLANK (),
        "__num", BLANK (),
        "__count", BLANK (),
        "__card_Month", [__card_Month],
        "__card_Partial", [__card_Partial],
        "__card_Empty", [__card_Empty],
        "__card_Sales", [__card_Sales],
        "__card_Margin", [__card_Margin],
        "__card_Orders", [__card_Orders],
        "__card_AOV", [__card_AOV],
        "__card_SalesCompare", [__card_SalesCompare],
        "__card_Profit", [__card_Profit],
        "__card_OrdersCompare", [__card_OrdersCompare],
        "__card_YoYCompare", [__card_YoYCompare]
    ),
    SELECTCOLUMNS (
        SUMMARIZECOLUMNS (
            __ValueFilterDM0_trend,
            "AW Trend Title", IGNORE ( 'FactInternetSales'[AW Trend Title] )
        ),
        "__slot", "trend",
        "__part", 0,
        "__label", BLANK (),
        "__date", BLANK (),
        "__sales", BLANK (),
        "__margin", BLANK (),
        "__orders", BLANK (),
        "__partial", BLANK (),
        "__text", [AW Trend Title],
        "__num", BLANK (),
        "__count", COALESCE ( COUNTROWS ( __DS0PrimaryWindowed_trend ), 0 ),
        "__card_Month", BLANK (),
        "__card_Partial", BLANK (),
        "__card_Empty", BLANK (),
        "__card_Sales", BLANK (),
        "__card_Margin", BLANK (),
        "__card_Orders", BLANK (),
        "__card_AOV", BLANK (),
        "__card_SalesCompare", BLANK (),
        "__card_Profit", BLANK (),
        "__card_OrdersCompare", BLANK (),
        "__card_YoYCompare", BLANK ()
    ),
    SELECTCOLUMNS (
        __DS0PrimaryWindowed_trend,
        "__slot", "trend",
        "__part", 1,
        "__label", 'DimDate'[YearMonth],
        "__date", 'DimDate'[YearMonthKey],
        "__sales", [Total_Sales],
        "__margin", [Gross_Margin],
        "__orders", [Order_Count],
        "__partial", [Partial_Month_Text],
        "__text", BLANK (),
        "__num", BLANK (),
        "__count", BLANK (),
        "__card_Month", BLANK (),
        "__card_Partial", BLANK (),
        "__card_Empty", BLANK (),
        "__card_Sales", BLANK (),
        "__card_Margin", BLANK (),
        "__card_Orders", BLANK (),
        "__card_AOV", BLANK (),
        "__card_SalesCompare", BLANK (),
        "__card_Profit", BLANK (),
        "__card_OrdersCompare", BLANK (),
        "__card_YoYCompare", BLANK ()
    ),
    SELECTCOLUMNS (
        SUMMARIZECOLUMNS (
            __DS0FilterTable_countries,
            "AW Country Title", IGNORE ( 'FactInternetSales'[AW Country Title] )
        ),
        "__slot", "countries",
        "__part", 0,
        "__label", BLANK (),
        "__date", BLANK (),
        "__sales", BLANK (),
        "__margin", BLANK (),
        "__orders", BLANK (),
        "__partial", BLANK (),
        "__text", [AW Country Title],
        "__num", BLANK (),
        "__count", COALESCE ( COUNTROWS ( __DS0PrimaryWindowed_countries ), 0 ),
        "__card_Month", BLANK (),
        "__card_Partial", BLANK (),
        "__card_Empty", BLANK (),
        "__card_Sales", BLANK (),
        "__card_Margin", BLANK (),
        "__card_Orders", BLANK (),
        "__card_AOV", BLANK (),
        "__card_SalesCompare", BLANK (),
        "__card_Profit", BLANK (),
        "__card_OrdersCompare", BLANK (),
        "__card_YoYCompare", BLANK ()
    ),
    SELECTCOLUMNS (
        __DS0PrimaryWindowed_countries,
        "__slot", "countries",
        "__part", 1,
        "__label", 'DimSalesTerritory'[SalesTerritoryCountry],
        "__date", BLANK (),
        "__sales", [Total_Sales],
        "__margin", [Gross_Margin],
        "__orders", [Order_Count],
        "__partial", BLANK (),
        "__text", BLANK (),
        "__num", BLANK (),
        "__count", BLANK (),
        "__card_Month", BLANK (),
        "__card_Partial", BLANK (),
        "__card_Empty", BLANK (),
        "__card_Sales", BLANK (),
        "__card_Margin", BLANK (),
        "__card_Orders", BLANK (),
        "__card_AOV", BLANK (),
        "__card_SalesCompare", BLANK (),
        "__card_Profit", BLANK (),
        "__card_OrdersCompare", BLANK (),
        "__card_YoYCompare", BLANK ()
    ),
    SELECTCOLUMNS (
        SUMMARIZECOLUMNS (
            __DS0FilterTable_categories,
            "AW Category Title", IGNORE ( 'FactInternetSales'[AW Category Title] )
        ),
        "__slot", "categories",
        "__part", 0,
        "__label", BLANK (),
        "__date", BLANK (),
        "__sales", BLANK (),
        "__margin", BLANK (),
        "__orders", BLANK (),
        "__partial", BLANK (),
        "__text", [AW Category Title],
        "__num", BLANK (),
        "__count", COALESCE ( COUNTROWS ( __DS0PrimaryWindowed_categories ), 0 ),
        "__card_Month", BLANK (),
        "__card_Partial", BLANK (),
        "__card_Empty", BLANK (),
        "__card_Sales", BLANK (),
        "__card_Margin", BLANK (),
        "__card_Orders", BLANK (),
        "__card_AOV", BLANK (),
        "__card_SalesCompare", BLANK (),
        "__card_Profit", BLANK (),
        "__card_OrdersCompare", BLANK (),
        "__card_YoYCompare", BLANK ()
    ),
    SELECTCOLUMNS (
        __DS0PrimaryWindowed_categories,
        "__slot", "categories",
        "__part", 1,
        "__label", 'DimProductCategory'[EnglishProductCategoryName],
        "__date", BLANK (),
        "__sales", [Total_Sales],
        "__margin", [Gross_Margin],
        "__orders", [Order_Count],
        "__partial", BLANK (),
        "__text", BLANK (),
        "__num", BLANK (),
        "__count", BLANK (),
        "__card_Month", BLANK (),
        "__card_Partial", BLANK (),
        "__card_Empty", BLANK (),
        "__card_Sales", BLANK (),
        "__card_Margin", BLANK (),
        "__card_Orders", BLANK (),
        "__card_AOV", BLANK (),
        "__card_SalesCompare", BLANK (),
        "__card_Profit", BLANK (),
        "__card_OrdersCompare", BLANK (),
        "__card_YoYCompare", BLANK ()
    )
)
ORDER BY
    [__slot],
    [__part],
    [__date],
    [__sales] DESC,
    [__label]

4-query plan: 12-month trend

Returns monthly sales, orders, margin, and coverage notes for the app’s fixed 12-month window, ordered chronologically.

Connector output: 12 measured rows × 7 columns in one returned table.

Returned columns: DimDate[YearMonth], DimDate[YearMonthKey], [Sales], [Orders], [Margin], [WithinWindow], [Partial]

Executed DAX (formatted by SQLBI)

EVALUATE
FILTER (
    SUMMARIZECOLUMNS (
        'DimDate'[YearMonth],
        'DimDate'[YearMonthKey],
        "Sales", [Total Sales],
        "Orders", [Order Count],
        "Margin", [Gross Margin],
        "WithinWindow", [Trend Window],
        "Partial", [Partial Month Text]
    ),
    [WithinWindow] = 1
)
ORDER BY 'DimDate'[YearMonthKey] ASC

4-query plan: Category chart

Returns December sales, orders, and margin by product category, ordered by sales descending and category name ascending.

Connector output: 3 measured rows × 4 columns in one returned table.

Returned columns: DimProductCategory[EnglishProductCategoryName], [Sales], [Orders], [Margin]

Executed DAX (formatted by SQLBI)

EVALUATE
SUMMARIZECOLUMNS (
    'DimProductCategory'[EnglishProductCategoryName],
    TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] ),
    "Sales", [Total Sales],
    "Orders", [Order Count],
    "Margin", [Gross Margin]
)
ORDER BY
    [Sales] DESC,
    'DimProductCategory'[EnglishProductCategoryName] ASC

4-query plan: Country chart

Returns December sales, orders, and margin by country, ordered by sales descending and country name ascending.

Connector output: 6 measured rows × 4 columns in one returned table.

Returned columns: DimSalesTerritory[SalesTerritoryCountry], [Sales], [Orders], [Margin]

Executed DAX (formatted by SQLBI)

EVALUATE
SUMMARIZECOLUMNS (
    'DimSalesTerritory'[SalesTerritoryCountry],
    TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] ),
    "Sales", [Total Sales],
    "Orders", [Order Count],
    "Margin", [Gross Margin]
)
ORDER BY
    [Sales] DESC,
    'DimSalesTerritory'[SalesTerritoryCountry] ASC

4-query plan: KPI cards

Returns December sales, cost, profit, margin, orders, average order value, comparison values and labels, and data coverage fields in one row.

Connector output: 1 measured row × 14 columns in one returned table.

Returned columns: [Sales], [Cost], [Profit], [Margin], [Orders], [Lines], [AOV], [SalesMoM], [SalesYoY], [SalesCompare], [OrdersCompare], [YoYCompare], [Partial], [SourceLastDate]

Executed DAX (formatted by SQLBI)

EVALUATE
CALCULATETABLE (
    ROW (
        "Sales", [Total Sales],
        "Cost", [Total Cost],
        "Profit", [Gross Profit],
        "Margin", [Gross Margin],
        "Orders", [Order Count],
        "Lines", [Order Lines],
        "AOV", [AOV],
        "SalesMoM", [Sales MoM %],
        "SalesYoY", [Sales YoY %],
        "SalesCompare", [Sales Compare Text],
        "OrdersCompare", [Orders Compare Text],
        "YoYCompare", [Sales YoY Compare Text],
        "Partial", [Partial Month Text],
        "SourceLastDate", [Source Last Date]
    ),
    TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )
)

2-query plan: Current KPIs + three charts

Packages eight current KPI and label outputs with the trend, country, and category titles and rows in one tagged table.

Connector output: 25 measured rows × 19 columns in one returned table.

Returned columns: [__slot], [__part], [__label], [__date], [__sales], [__margin], [__orders], [__partial], [__text], [__num], [__count], [__card_Month], [__card_Partial], [__card_Empty], [__card_Sales], [__card_Margin], [__card_Orders], [__card_AOV], [__card_Profit]

Executed DAX (formatted by SQLBI)

DEFINE
    MEASURE 'FactInternetSales'[AW Selected Month] =
        (
            /* USER DAX BEGIN */
            VAR ym =
                SELECTEDVALUE ( DimDate[YearMonthKey] )
            RETURN
                IF (
                    ISBLANK ( ym ),
                    "Select one month",
                    FORMAT ( DATE ( INT ( ym / 100 ), MOD ( ym, 100 ), 1 ), "MMM yyyy" )
                ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Partial Badge] =
        ( /* USER DAX BEGIN */
        COALESCE ( [Partial Month Text], "" ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Empty Text] =
        (
            /* USER DAX BEGIN */
            IF (
                COALESCE ( [Order Lines], 0 ) = 0,
                "No sales for this selection",
                ""
            ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Card Sales] =
        (
            /* USER DAX BEGIN */
            VAR v = [Total Sales]
            RETURN
                IF (
                    ISBLANK ( v ),
                    "—",
                    IF (
                        ABS ( v ) >= 1000000,
                        FORMAT ( v / 1000000, "$0.0" ) & "M",
                        IF (
                            ABS ( v ) >= 1000,
                            FORMAT ( v / 1000, "$0.0" ) & "K",
                            FORMAT ( v, "$0.00" )
                        )
                    )
                ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Card Margin] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Gross Margin] ),
                "—",
                FORMAT ( [Gross Margin], "0.0%" )
            ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Card Orders] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Order Count] ),
                "—",
                FORMAT ( [Order Count], "#,0" )
            ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Card AOV] =
        (
            /* USER DAX BEGIN */
            IF ( ISBLANK ( [AOV] ), "—", FORMAT ( [AOV], "$#,0.00" ) ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Trend Title] =
        (
            /* USER DAX BEGIN */
            VAR hi = [Source Last Date]
            VAR lo =
                EDATE ( DATE ( YEAR ( hi ), MONTH ( hi ), 1 ), -11 )
            VAR n =
                CALCULATE (
                    [Order Lines],
                    FILTER (
                        ALL ( DimDate ),
                        DimDate[FullDateAlternateKey] >= lo
                            && DimDate[FullDateAlternateKey] <= EOMONTH ( hi, 0 )
                    )
                )
            VAR coverage =
                CALCULATE (
                    [Partial Month Text],
                    REMOVEFILTERS ( DimDate ),
                    TREATAS ( { FORMAT ( hi, "yyyy-MM" ) }, DimDate[YearMonth] )
                )
            RETURN
                "12-month trend · " & FORMAT ( lo, "MMM yyyy" ) & "–"
                    & FORMAT ( hi, "MMM yyyy" )
                    & IF (
                        COALESCE ( n, 0 ) = 0,
                        " · No data for the current filters — clear Where/What or Reset",
                        ""
                    )
                    & IF ( NOT ISBLANK ( coverage ), " · " & coverage, "" ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Country Title] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Total Sales] ),
                "No data for the current filters",
                "Where · Country — click to filter; right-click to drill through"
            ) /* USER DAX END */
        )
    MEASURE 'FactInternetSales'[AW Category Title] =
        (
            /* USER DAX BEGIN */
            IF (
                ISBLANK ( [Total Sales] ),
                "No data for the current filters",
                "What · Category — click to filter; right-click to drill through"
            ) /* USER DAX END */
        )
    VAR __CardsFilter =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )
    VAR __ValueFilterDM0_trend =
        FILTER (
            KEEPFILTERS (
                SUMMARIZECOLUMNS (
                    'DimDate'[YearMonth],
                    'DimDate'[YearMonthKey],
                    "Trend_Window", IGNORE ( 'FactInternetSales'[Trend Window] )
                )
            ),
            [Trend_Window] = 1
        )
    VAR __DS0Core_trend =
        SUMMARIZECOLUMNS (
            'DimDate'[YearMonth],
            'DimDate'[YearMonthKey],
            __ValueFilterDM0_trend,
            "Total_Sales", 'FactInternetSales'[Total Sales],
            "Order_Count", IGNORE ( 'FactInternetSales'[Order Count] ),
            "Gross_Margin", IGNORE ( 'FactInternetSales'[Gross Margin] ),
            "Partial_Month_Text", IGNORE ( 'FactInternetSales'[Partial Month Text] )
        )
    VAR __DS0PrimaryShowAll_trend =
        ADDMISSINGITEMS ( 'DimDate'[YearMonth],
        'DimDate'[YearMonthKey],
        __DS0Core_trend,
        'DimDate'[YearMonth],
        'DimDate'[YearMonthKey],
        __ValueFilterDM0_trend )
    VAR __DS0PrimaryWindowed_trend =
        TOPN (
            1001,
            __DS0PrimaryShowAll_trend,
            'DimDate'[YearMonthKey], 1,
            'DimDate'[YearMonth], 1
        )
    VAR __DS0FilterTable_countries =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )
    VAR __DS0Core_countries =
        SUMMARIZECOLUMNS (
            'DimSalesTerritory'[SalesTerritoryCountry],
            __DS0FilterTable_countries,
            "Total_Sales", 'FactInternetSales'[Total Sales],
            "Gross_Margin", IGNORE ( 'FactInternetSales'[Gross Margin] ),
            "Order_Count", IGNORE ( 'FactInternetSales'[Order Count] )
        )
    VAR __DS0PrimaryWindowed_countries =
        TOPN (
            1001,
            __DS0Core_countries,
            [Total_Sales], 0,
            'DimSalesTerritory'[SalesTerritoryCountry], 1
        )
    VAR __DS0FilterTable_categories =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )
    VAR __DS0Core_categories =
        SUMMARIZECOLUMNS (
            'DimProductCategory'[EnglishProductCategoryName],
            __DS0FilterTable_categories,
            "Total_Sales", 'FactInternetSales'[Total Sales],
            "Gross_Margin", IGNORE ( 'FactInternetSales'[Gross Margin] ),
            "Order_Count", IGNORE ( 'FactInternetSales'[Order Count] )
        )
    VAR __DS0PrimaryWindowed_categories =
        TOPN (
            1001,
            __DS0Core_categories,
            [Total_Sales], 0,
            'DimProductCategory'[EnglishProductCategoryName], 1
        )
    VAR __Wide =
        SUMMARIZECOLUMNS (
            __CardsFilter,
            "__card_Month", IGNORE ( 'FactInternetSales'[AW Selected Month] ),
            "__card_Partial", IGNORE ( 'FactInternetSales'[AW Partial Badge] ),
            "__card_Empty", IGNORE ( 'FactInternetSales'[AW Empty Text] ),
            "__card_Sales", IGNORE ( 'FactInternetSales'[AW Card Sales] ),
            "__card_Margin", IGNORE ( 'FactInternetSales'[AW Card Margin] ),
            "__card_Orders", IGNORE ( 'FactInternetSales'[AW Card Orders] ),
            "__card_AOV", IGNORE ( 'FactInternetSales'[AW Card AOV] ),
            "__card_Profit", IGNORE ( 'FactInternetSales'[Gross Profit] )
        )

EVALUATE
UNION (
    SELECTCOLUMNS (
        __Wide,
        "__slot", "cards",
        "__part", 0,
        "__label", BLANK (),
        "__date", BLANK (),
        "__sales", BLANK (),
        "__margin", BLANK (),
        "__orders", BLANK (),
        "__partial", BLANK (),
        "__text", BLANK (),
        "__num", BLANK (),
        "__count", BLANK (),
        "__card_Month", [__card_Month],
        "__card_Partial", [__card_Partial],
        "__card_Empty", [__card_Empty],
        "__card_Sales", [__card_Sales],
        "__card_Margin", [__card_Margin],
        "__card_Orders", [__card_Orders],
        "__card_AOV", [__card_AOV],
        "__card_Profit", [__card_Profit]
    ),
    SELECTCOLUMNS (
        SUMMARIZECOLUMNS (
            __ValueFilterDM0_trend,
            "AW Trend Title", IGNORE ( 'FactInternetSales'[AW Trend Title] )
        ),
        "__slot", "trend",
        "__part", 0,
        "__label", BLANK (),
        "__date", BLANK (),
        "__sales", BLANK (),
        "__margin", BLANK (),
        "__orders", BLANK (),
        "__partial", BLANK (),
        "__text", [AW Trend Title],
        "__num", BLANK (),
        "__count", COALESCE ( COUNTROWS ( __DS0PrimaryWindowed_trend ), 0 ),
        "__card_Month", BLANK (),
        "__card_Partial", BLANK (),
        "__card_Empty", BLANK (),
        "__card_Sales", BLANK (),
        "__card_Margin", BLANK (),
        "__card_Orders", BLANK (),
        "__card_AOV", BLANK (),
        "__card_Profit", BLANK ()
    ),
    SELECTCOLUMNS (
        __DS0PrimaryWindowed_trend,
        "__slot", "trend",
        "__part", 1,
        "__label", 'DimDate'[YearMonth],
        "__date", 'DimDate'[YearMonthKey],
        "__sales", [Total_Sales],
        "__margin", [Gross_Margin],
        "__orders", [Order_Count],
        "__partial", [Partial_Month_Text],
        "__text", BLANK (),
        "__num", BLANK (),
        "__count", BLANK (),
        "__card_Month", BLANK (),
        "__card_Partial", BLANK (),
        "__card_Empty", BLANK (),
        "__card_Sales", BLANK (),
        "__card_Margin", BLANK (),
        "__card_Orders", BLANK (),
        "__card_AOV", BLANK (),
        "__card_Profit", BLANK ()
    ),
    SELECTCOLUMNS (
        SUMMARIZECOLUMNS (
            __DS0FilterTable_countries,
            "AW Country Title", IGNORE ( 'FactInternetSales'[AW Country Title] )
        ),
        "__slot", "countries",
        "__part", 0,
        "__label", BLANK (),
        "__date", BLANK (),
        "__sales", BLANK (),
        "__margin", BLANK (),
        "__orders", BLANK (),
        "__partial", BLANK (),
        "__text", [AW Country Title],
        "__num", BLANK (),
        "__count", COALESCE ( COUNTROWS ( __DS0PrimaryWindowed_countries ), 0 ),
        "__card_Month", BLANK (),
        "__card_Partial", BLANK (),
        "__card_Empty", BLANK (),
        "__card_Sales", BLANK (),
        "__card_Margin", BLANK (),
        "__card_Orders", BLANK (),
        "__card_AOV", BLANK (),
        "__card_Profit", BLANK ()
    ),
    SELECTCOLUMNS (
        __DS0PrimaryWindowed_countries,
        "__slot", "countries",
        "__part", 1,
        "__label", 'DimSalesTerritory'[SalesTerritoryCountry],
        "__date", BLANK (),
        "__sales", [Total_Sales],
        "__margin", [Gross_Margin],
        "__orders", [Order_Count],
        "__partial", BLANK (),
        "__text", BLANK (),
        "__num", BLANK (),
        "__count", BLANK (),
        "__card_Month", BLANK (),
        "__card_Partial", BLANK (),
        "__card_Empty", BLANK (),
        "__card_Sales", BLANK (),
        "__card_Margin", BLANK (),
        "__card_Orders", BLANK (),
        "__card_AOV", BLANK (),
        "__card_Profit", BLANK ()
    ),
    SELECTCOLUMNS (
        SUMMARIZECOLUMNS (
            __DS0FilterTable_categories,
            "AW Category Title", IGNORE ( 'FactInternetSales'[AW Category Title] )
        ),
        "__slot", "categories",
        "__part", 0,
        "__label", BLANK (),
        "__date", BLANK (),
        "__sales", BLANK (),
        "__margin", BLANK (),
        "__orders", BLANK (),
        "__partial", BLANK (),
        "__text", [AW Category Title],
        "__num", BLANK (),
        "__count", COALESCE ( COUNTROWS ( __DS0PrimaryWindowed_categories ), 0 ),
        "__card_Month", BLANK (),
        "__card_Partial", BLANK (),
        "__card_Empty", BLANK (),
        "__card_Sales", BLANK (),
        "__card_Margin", BLANK (),
        "__card_Orders", BLANK (),
        "__card_AOV", BLANK (),
        "__card_Profit", BLANK ()
    ),
    SELECTCOLUMNS (
        __DS0PrimaryWindowed_categories,
        "__slot", "categories",
        "__part", 1,
        "__label", 'DimProductCategory'[EnglishProductCategoryName],
        "__date", BLANK (),
        "__sales", [Total_Sales],
        "__margin", [Gross_Margin],
        "__orders", [Order_Count],
        "__partial", BLANK (),
        "__text", BLANK (),
        "__num", BLANK (),
        "__count", BLANK (),
        "__card_Month", BLANK (),
        "__card_Partial", BLANK (),
        "__card_Empty", BLANK (),
        "__card_Sales", BLANK (),
        "__card_Margin", BLANK (),
        "__card_Orders", BLANK (),
        "__card_AOV", BLANK (),
        "__card_Profit", BLANK ()
    )
)
ORDER BY
    [__slot],
    [__part],
    [__date],
    [__sales] DESC,
    [__label]

2-query plan: Historical comparisons

Shares prior-period calculations across the sales and orders month-over-month comparisons and the sales year-over-year comparison.

Connector output: 1 measured row × 3 columns in one returned table.

Returned columns: [__card_SalesCompare], [__card_OrdersCompare], [__card_YoYCompare]

Executed DAX (formatted by SQLBI)

DEFINE
    VAR __CardsFilter =
        TREATAS ( { "2013-12" }, 'DimDate'[YearMonth] )

EVALUATE
CALCULATETABLE (
    VAR selectedYM = [Selected YM]
    VAR minimumYM =
        INT ( [First Source Date Key] / 100 )
    VAR selectedPartial = [Is Partial Selected Month]
    VAR pmYM = [Prev YM]
    VAR pyYM = [Prior Year YM]
    VAR pmPartial = [Is Partial Prev Month]
    VAR pyPartial = [Is Partial Prior Year Month]
    VAR nowSales = [Total Sales]
    VAR nowOrders = [Order Count]
    VAR pmSales = [Sales PM]
    VAR pmOrders = [Orders PM]
    VAR pySales = [Sales PY]
    VAR __valueSalesCompare =
        VAR change =
            IF (
                NOT selectedPartial && NOT pmPartial && NOT ISBLANK ( nowSales ),
                DIVIDE ( nowSales - pmSales, pmSales )
            )
        VAR label =
            IF (
                NOT ISBLANK ( pmYM ),
                FORMAT ( DATE ( INT ( pmYM / 100 ), MOD ( pmYM, 100 ), 1 ), "mmm yyyy" )
            )
        VAR resultText =
            SWITCH (
                TRUE (),
                ISBLANK ( selectedYM ) || ISBLANK ( nowSales ), BLANK (),
                pmYM < minimumYM, "No comparison: no earlier data",
                selectedPartial, "No comparison: selected month partial",
                pmPartial, "No comparison: prior month partial",
                ISBLANK ( pmSales ) || pmSales = 0, "No comparison: no prior matching sales",
                IF ( change >= 0, "+", "" ) & FORMAT ( change, "0.0%" ) & " vs " & label
            )
        RETURN
            IF (
                ISBLANK ( change ),
                resultText,
                IF ( change >= 0, "▲ ", "▼ " ) & resultText
            )
    VAR __valueOrdersCompare =
        VAR change =
            IF (
                NOT selectedPartial && NOT pmPartial && NOT ISBLANK ( nowOrders ),
                DIVIDE ( nowOrders - pmOrders, pmOrders )
            )
        VAR label =
            IF (
                NOT ISBLANK ( pmYM ),
                FORMAT ( DATE ( INT ( pmYM / 100 ), MOD ( pmYM, 100 ), 1 ), "mmm yyyy" )
            )
        VAR resultText =
            SWITCH (
                TRUE (),
                ISBLANK ( selectedYM ) || ISBLANK ( nowOrders ), BLANK (),
                pmYM < minimumYM, "No comparison: no earlier data",
                selectedPartial, "No comparison: selected month partial",
                pmPartial, "No comparison: prior month partial",
                ISBLANK ( pmOrders ) || pmOrders = 0, "No comparison: no prior matching sales",
                IF ( change >= 0, "+", "" ) & FORMAT ( change, "0.0%" ) & " vs " & label
            )
        RETURN
            IF (
                ISBLANK ( change ),
                resultText,
                IF ( change >= 0, "▲ ", "▼ " ) & resultText
            )
    VAR __valueYoYCompare =
        VAR change =
            IF (
                NOT selectedPartial && NOT pyPartial && NOT ISBLANK ( nowSales ),
                DIVIDE ( nowSales - pySales, pySales )
            )
        VAR label =
            IF (
                NOT ISBLANK ( pyYM ),
                FORMAT ( DATE ( INT ( pyYM / 100 ), MOD ( pyYM, 100 ), 1 ), "mmm yyyy" )
            )
        VAR resultText =
            SWITCH (
                TRUE (),
                ISBLANK ( selectedYM ) || ISBLANK ( nowSales ), BLANK (),
                pyYM < minimumYM, "No comparison: no earlier data",
                selectedPartial, "No comparison: selected month partial",
                pyPartial, "No comparison: prior month partial",
                ISBLANK ( pySales ) || pySales = 0, "No comparison: no prior matching sales",
                IF ( change >= 0, "+", "" ) & FORMAT ( change, "0.0%" ) & " vs " & label
            )
        RETURN
            IF (
                ISBLANK ( change ),
                resultText,
                IF ( change >= 0, "▲ ", "▼ " ) & resultText
            )
    RETURN
        ROW (
            "__card_SalesCompare", __valueSalesCompare,
            "__card_OrdersCompare", __valueOrdersCompare,
            "__card_YoYCompare", __valueYoYCompare
        ),
    __CardsFilter
)