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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
powerbi666
Regular Visitor

A puzzle about KEEPFILTERS & arbitrarily shaped filters

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? 
 

下载.png

 

 

 
10 REPLIES 10
daxer-almighty
Solution Sage
Solution Sage

@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.

mahoneypat
Employee
Employee

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] )

 

mahoneypat_1-1620504169967.png

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


CNENFRNL
Community Champion
Community Champion

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.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@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.

@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

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@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.

OwenAuger
Super User
Super User

@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:

  1. Filter context of matrix overall: (yr=2020,mth=1) || (yr=2021,mth=2)
  2. 2020 row of matrix applies filter of (yr=2020) when evaluating measure.
  3. This should cause "complex filter reduction", resulting in (yr=2020) && ( (mth=1) || (mth = 2) )
  4. So I would expect, for 2020, [SUMX of Amount] = 1 + 3 = 4
  5. 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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
mahoneypat
Employee
Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors