returning blank values when using SUMMARIZE and ADDCOLUMNS

Hi YYS 

The underlying reason that Q_previous2 returns blank values while Q_previous3 works fine for certan combinations of Continent and Brand is that

SUMMARIZE ( sales, customer[continent], 'product'[brand] )

returns the distinct combinations of customer[continent] and 'product'[brand] corresponding to existing rows of sales in a given filter context.

For example, for this combination of filters (i.e. filter context):

• 'date'[Year] = 2024
• customer[continent] = "Australia"
• 'product'[brand] = "Litware"

sales contains no rows (evident because [Q] returns blank for Australia/Litware in 2024).

Therefore:

1. SUMMARIZE ( sales, customer[continent], 'product'[brand] ) evaluated within the same filter context returns an empty table.
2. SUMX over an empty table returns blank.
3. So Q_previous2 returns blank.

Put another way, continent/brand combinations that don't existing in 2024 do not appear in SUM_TABLE, and therefore no "previous year" value is computed for those combinations.

If you do need to evaluate a particular measure at the continent/brand granularity and then sum, then some options are:

1. Use CROSSJOIN and VALUES to produce the combinations of customer[continent] and 'product'[brand]:

Q_previous2 =
VAR SUM_TABLE =
    CROSSJOIN ( VALUES ( customer[continent] ), VALUES ( 'product'[brand] ) )
VAR ADD_TABLE =
    ADDCOLUMNS ( SUM_TABLE, "Quantity", [Q], "PreviousQuantity", [Q_previous1] )
RETURN
    SUMX ( ADD_TABLE, [PreviousQuantity] )

2. Use SUMMARIZECOLUMNS to both produce the combinations and add the "PreviousQuantity" column (SUMMARIZECOLUMNS previously didn't work within measures in all cases):

Q_previous2 =
VAR ADD_TABLE =
    SUMMARIZECOLUMNS (
        'customer'[Continent],
        'product'[Brand],
        "PreviousQuantity", [Q_previous1]
    )
RETURN
    SUMX ( ADD_TABLE, [PreviousQuantity] )

3. Refactor by first creating a measure that aggregates [Q] by continent/brand, and then another measure that computes this measure for the previous year:

Q summed by product and brand =
SUMX (
    SUMMARIZE (
        sales,
        customer[Continent],
        'product'[Brand]
    ),
    [Q]
)

Q summed by product and brand Previous
CALCULATE (
    [Q],
    DATEADD ( 'date'[Date], -1, YEAR )
)

I note that the aggregation by continent/brand is not required for a simple sum of fact table values, but it could be required for a more exotic measure.

Hopefully this is useful!