Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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:
Solved! Go to 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:
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.
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:
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.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
120 | |
112 | |
60 | |
58 |