Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
@mahoneypat, @CNENFRNL, @OwenAuger, @powerbi666
Guys, if you want to know the quirks of SUMMARIZECOLUMNS (I think this is applicable in this case), please read an article on LinkedIn by my work colleague. One that was inspired by me doing experiments with different filters in SUMMARIZECOLUMNS during my work hours:
https://www.linkedin.com/pulse/peculiar-behavior-summarizecolumns-dax-abhinav-khanduja/
Hope this will help in unraveling the mystery.
FYI that I reproduced this scenario in a similar model. The two visuals below are independently filtered (no crossfiltering). The SUMMARIZE approach works well to avoid the observed behavior and give the correct results. Using YearMonth (single column) in the slicer avoids it too. With your measure (Sumx Values Year), it looks like the Year filter is overiding the Filter on Year from the slicer at the visual level, so the sum of all years for the month in that year is obtained. I wouldn't have predicted that weird behavior with hierarchies in the slicer.
SummarizeYM =
SUMX ( SUMMARIZE ( 'Date', 'Date'[Year], 'Date'[Monthnumber] ), [Total Sales] )
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Interesting, I'm not alone; I've dwelled on this seemingly easy, but head-scratching puzzle since this morning. Disappointedly, no solution yet.
I really appreciate @OwenAuger 's methodology; and hope other gurus contribute fancy ideas to this issue.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
SUMMARIZECOLUMNS is a very complex function (contrary to common belief) when you put filters inside it. Please refer to the documentation and - best - experiment with it yourself to see that sometimes it behaves not how one would expect but still according to the specification.
@daxer-almighty Wow, that's a great article, going to reread a few times, thanks for that!
I understand from asking around yesterday that Microsoft is treating this particular behaviour as a bug, so I'm guessing there will be an update at some stage.
Would you please report THIS to Microsoft as well? I'm sure this is a bug and one of a big calibre at that. I would do it but don't have a Pro license, so I'm afraid it wouldn't reach MS quickly.
Many thanks.
@daxer-almighty - thanks, another interesting one to get my head around!
Sure, will try to pass that through and see what response we get.
Thanks again,
Owen
Yeah, I too think it is a very good article that anyone who works with PBI should read. First I was to write it but didn't have enough time (at that time), so Abhinav did it after I had given him a little nugde. I'll ask him if he'll let me publish it here since I think it would be beneficial to the Community.
However, I very much doubt that Microsoft are going to change anything in the behaviour of SUMMARIZECOLUMNS. There are simply too many reports in the world right now that rely on this function working the way it does... It's the same thing as with "the Excel bug," if you know what I mean.
A puzzle indeed!
I agree, this result is not what I would expect either!
Your reasoning looks correct to me:
Restating what you have already said, in the matrix, for the 2020 subtotal of [SUMX of Amount], we should have:
I'm leaning towards this being a bug, or certainly some oddity of how SUMMARIZCOLUMNS behaves.
To further explore, I looked at the DAX query behind the visual.
If we simplify the visual to just show [SUMX of Amount] by Sales[Year] and look at the DAX query generated by the visual, the query essentially looks like this:
DAX Query generated by Power BI (simplified)
EVALUATE
VAR ComplexFilter =
TREATAS ( { ( 2020, 1 ), ( 2021, 2 ) }, 'Sales'[Year], 'Sales'[Month] )
RETURN
SUMMARIZECOLUMNS (
'Sales'[Year],
ComplexFilter,
"SUMX_of_Amount", [SUMX of Amount]
)
Year | SUMX_of_Amount |
2020 | 6 |
2021 | 10 |
However if we write essentially the same query using SUMMARIZE/ADDCOLUMNS:
Similar DAX query with SUMMARIZE/ADDCOLUMNS:
EVALUATE
VAR ComplexFilter =
TREATAS ( { ( 2020, 1 ), ( 2021, 2 ) }, 'Sales'[Year], 'Sales'[Month] )
RETURN
CALCULATETABLE (
ADDCOLUMNS (
-- Just get totals by year
SUMMARIZE ( Sales, Sales[Year] ),
"SUMX_of_Amount", [SUMX of Amount]
),
ComplexFilter
)
Year | SUMX_of_Amount |
2020 | 4 |
2021 | 12 |
I don't have an explanation but I will look at this further and see if I can find some answers.
I have attached my PBIX containing your sample data.
Regards,
Owen
Another approach to consider is to use a measure expression like this
SUMX Total Sales = SUMX(SUMMARIZE('Date', 'Date'[Year], 'Date'[Month]), [Sum of Amount])
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
47 | |
27 | |
22 | |
17 | |
15 |
User | Count |
---|---|
54 | |
31 | |
18 | |
16 | |
15 |