Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
seanma
New Member

Incorrect results from DAX SUMMARIZE with dual relationships

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  
IdName 
1AA 
2BB 
   
Sales  
ItemIdQuarterValue
111
122
214
   
QuarterItems 
QuarterItemId 
11 
12 
21 
   
AccumulatedQuarters 
AccumulatedQuartersQuarter 
11 
21 
22 
31 
32 
33 
41 
42 
43 
44 

 

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

seanma_0-1709000782774.png

 

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_1-1709003988932.png

 

1 ACCEPTED SOLUTION
seanma
New Member

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:
seanma_0-1709179866833.png

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.

View solution in original post

3 REPLIES 3
seanma
New Member

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:
seanma_0-1709179866833.png

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.

v-yaningy-msft
Community Support
Community Support

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

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.