topic Re: returning blank values when using SUMMARIZE and ADDCOLUMNS in DAX Commands and Tips

returning blank values when using SUMMARIZE and ADDCOLUMNS

YYS — Sun, 18 May 2025 15:43:09 GMT

Hello,

I am experiencing an issue with my DAX calculation in Power BI.
I have attached a sample PBIX file for your reference.

Here’s the situation:

I have a fact table named 'sales' and dimension tables named 'customer', 'product', and 'date'.
The relationships are as follows: 'sales'[CustomerKey] is related to 'customer'[CustomerKey], and 'sales'[ProductKey] is related to 'product'[ProductKey].
I am trying to calculate the previous year's quantity for each combination of 'customer'[Continent] and 'product'[Brand].

My measures are defined as below:

Q = SUM('sales'[Quantity])

Q_previous1 = CALCULATE([Q], DATEADD('date'[Date], -1, YEAR))

Now, I want to aggregate previous quantities by continent and brand.
I tried the following measure (Q_previous2):

Q_previous2 =

VAR SUM_TABLE =

summarize(

'sales',

'customer'[Continent],

'product'[Brand]

)

VAR ADD_TABLE =

addcolumns(SUM_TABLE,"Quantity",[Q],"PreviousQuantity",[Q_previous1])

Return

SUMX(ADD_TABLE, [PreviousQuantity])

However, when I use this measure in a matrix visualization, I get blank values for "PreviousQuantity" in certain cases (for example, for Australia and Litware, where Q_previous1 correctly returns 15, Q_previous2 returns blank).

On the other hand, when I use VALUES instead of SUMMARIZE,
as in Q_previous3 below, the results are correct:

Q_previous3 =

VAR ADD_TABLE =

addcolumns(values('product'[Brand]),"Quantity",[Q],"PreviousQuantity",[Q_previous1])

Return

SUMX(ADD_TABLE, [PreviousQuantity])

My question:
Why is Q_previous2 returning blank values while Q_previous3 works fine?
How can I correctly calculate the previous year's quantity for each combination of 'customer'[Continent] and 'product'[Brand] using SUMMARIZE and ADDCOLUMNS?

Any advice or examples would be greatly appreciated.
Please refer to the attached PBIX file for details.

Thank you in advance!

Additional Note:
This sample file was created to simplify the question.
If I need to summarize only one column, using VALUES works fine.
However, when I need to summarize two columns, I have no choice but to use SUMMARIZE.
What I am looking for is a way to extract the previous year’s quantity from the table while summarizing two columns.

file download

Re: returning blank values when using SUMMARIZE and ADDCOLUMNS

OwenAuger — Mon, 19 May 2025 06:42:04 GMT

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:

SUMMARIZE ( sales, customer[continent], 'product'[brand] ) evaluated within the same filter context returns an empty table.
SUMX over an empty table returns blank.
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!

Re: returning blank values when using SUMMARIZE and ADDCOLUMNS

Anonymous — Mon, 19 May 2025 06:47:31 GMT

Hi YYS ,

Thank you for reaching out to Microsoft Fabric Community Forum.

OwenAuger Thank you for your quick response.

YYS

Alongside the solution provided by the super user, you may also consider trying the following DAX measure.

Q_previous2_mine =

VAR SUM_TABLE =

SUMMARIZE(

'sales',

'customer'[Continent],

'product'[Brand]

)

RETURN

CALCULATE(SUM(sales[Quantity]),DATEADD('date'[Date],-1,YEAR))

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

Re: returning blank values when using SUMMARIZE and ADDCOLUMNS

YYS — Mon, 19 May 2025 17:30:30 GMT

Thank you so much for your perfect solution.
I had been struggling with this issue for weeks, and I learned a lot from your answer.
I really appreciate your help.