topic Re: A puzzle about KEEPFILTERS & arbitrarily shaped filters in DAX Commands and Tips

A puzzle about KEEPFILTERS & arbitrarily shaped filters

powerbi666 — Sat, 08 May 2021 05:57:18 GMT

Hello,

I have learned the latest article about KEEPFILTERS & arbitrarily shaped filters on sqlbi.com，and the video on youtube.

According to the instructions，with the following case，the final filter context of subtotal row of year 2020 should be (year = 2020 , month = 1) || (year = 2020 , month = 2) ,but something seems to be wrong.

Could anybody help me?

Re: A puzzle about KEEPFILTERS & arbitrarily shaped filters

mahoneypat — Sat, 08 May 2021 12:43:04 GMT

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

Re: A puzzle about KEEPFILTERS & arbitrarily shaped filters

OwenAuger — Sat, 08 May 2021 13:51:10 GMT

powerbi666

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:

Filter context of matrix overall: (yr=2020,mth=1) || (yr=2021,mth=2)
2020 row of matrix applies filter of (yr=2020) when evaluating measure.
This should cause "complex filter reduction", resulting in (yr=2020) && ( (mth=1) || (mth = 2) )
So I would expect, for 2020, [SUMX of Amount] = 1 + 3 = 4
However, we are getting [SUMX of Amount] = 1 + 5 = 6
After some testing with different figures, I agree that this is apparently (yr=2020,mth=1) || (yr=2021,mth=1)

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

Re: A puzzle about KEEPFILTERS & arbitrarily shaped filters

CNENFRNL — Sat, 08 May 2021 15:24:12 GMT

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.

Re: A puzzle about KEEPFILTERS & arbitrarily shaped filters

daxer-almighty — Sat, 08 May 2021 16:05:32 GMT

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.

Re: A puzzle about KEEPFILTERS & arbitrarily shaped filters

mahoneypat — Sat, 08 May 2021 20:13:45 GMT

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

Re: A puzzle about KEEPFILTERS & arbitrarily shaped filters

daxer-almighty — Sat, 08 May 2021 22:30:08 GMT

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.

Re: A puzzle about KEEPFILTERS & arbitrarily shaped filters

OwenAuger — Sat, 08 May 2021 22:46:09 GMT

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.

Re: A puzzle about KEEPFILTERS & arbitrarily shaped filters

daxer-almighty — Sat, 08 May 2021 23:00:59 GMT

OwenAuger

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.

Re: A puzzle about KEEPFILTERS & arbitrarily shaped filters

daxer-almighty — Sun, 09 May 2021 01:42:36 GMT

OwenAuger

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.

Re: A puzzle about KEEPFILTERS & arbitrarily shaped filters

OwenAuger — Sun, 09 May 2021 02:32:44 GMT

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