Incorrect results from DAX SUMMARIZE with dual relationships

[seanma]

The Power BI Desktop file is available here: test_summarize_in_measure.pbix 

I have a simple model with a few tables:

- Items

- Sales

- QuarterItems

- AccumulatedQuarters

- Quarters

- Quarters2

Items
Id	Name
1	AA
2	BB
Sales
ItemId	Quarter	Value
1	1	1
1	2	2
2	1	4
QuarterItems
Quarter	ItemId
1	1
1	2
2	1
AccumulatedQuarters
AccumulatedQuarters	Quarter
1	1
2	1
2	2
3	1
3	2
3	3
4	1
4	2
4	3
4	4

And this is the model with the relationships between all the tables

The design was intended to get the aggregated sales for each item from Quarter 1 to the current quarter (accumulated quarter), only for items that exist in the current quarter. 

The two measures are:

- ItemSales = CALCULATE(SUM(Sales[Value]))

- ItemSales_Using_SUMMARIZE = SUMX(SUMMARIZE(Sales, Sales[ItemId], Sales[Quarter], "@val", [ItemSales]), [@val])

Slicer Quarter2 is used to select the items in the current quarter (Q2) and Slicer AccumulatedQuarter is used to filter the Sales table by mapping to the quarters (Q1 and Q2 to accumulated quarter 2).

Note that if the relationship between Quarters and QuarterItems is disactivated then all work as expected. However, if this relationship is activated, the results are only for Q2 - Q1 is missing. 

My questions is why the dual relationships (Quarters[Quarter] -> Sales[Quarter] and Quarters[Quarter] -> QuarterItems[Quarter] -> Sales[Quarter]) between Quarters to Sales will lead to unexpected results when using SUMMARIZE - Seems Slicer Quarter2 was used to filter the Sales table? 

Here are the incorrect results:

[seanma · Accepted solution]

Here I will provide the explanation by myself.

There is a great article all-the-secrets-of-summarize to explain how SUMMARIZE works and why we should not use the SUMMARIZE to create new columns.

The results are correct after changing the measure ItemSales_Using_SUMMARIZE from:

ItemSales_Using_SUMMARIZE = SUMX(SUMMARIZE(Sales, Sales[ItemId], Sales[Quarter], "@val", [ItemSales]), [@val])

to:

ItemSales_Using_SUMMARIZE = SUMX(ADDCOLUMNS(SUMMARIZE(Sales, Sales[ItemId], Sales[Quarter]), "@val", [ItemSales]), [@val])

Note that I used ADDCOLUMNS to add the new calculated column "@val".

The reason is simply that, when using the SUMMARIZE to add a new column, it creates a filter context using all the columns in the cluster (the explanded tables), filtering the values that are present in the cluster. 

In this case, the Quarter[quarter] column has been filtered by the selected value, 2, in Quarters2[Quarter]. Here we show the expanded table based on table QuarterItems and the filtered records in SUMMARIZE:

Obvious due to the relationship Quarters[Quarter] -> QuarterItems[Quarter], the Expanded (QuarterItems) table included the Quarters[Quarter] column and applied the filter based on the selected value from Quarters2[Quarter].

For more info about expanded table, please check another great article expanded-tables-in-dax.

Hope this helps.

[Anonymous]

Hi, @seanma 

According to the database design principle, you should try to use the star architecture design principle to avoid the data query confusion, in the database model you gave, he does not follow this principle, you can consider the relationship between Quarters[Quarter] -> Sales[Quarter] and Quarters[Quarter] -> QuarterItems[ Quarter] -> Sales[Quarter] relationship inactive and use the USERELATIONSHIP function to activate the relationship for use when you use it, you can refer to the following DAX.

ItemSales_Using_SUMMARIZE = 
CALCULATE(
    SUMX(
        SUMMARIZE(
            Sales, 
            Sales[ItemId], 
            Sales[Quarter], 
            "@val", 
            [ItemSales]
        ), 
        [@val]
    ),
    USERELATIONSHIP(Quarters[Quarter], Sales[Quarter])
)

Official Document: Model relationships in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[seanma]

Thanks. It's not always easy to follow the star architecture. Actually the relationship between Quarters[Quarter] -> QuarterItems[Quarter] is not required but I accidently kept this one during model update. My goal is to understand what happened so the results are not correct.