Forum Discussion
Difference between filter and filter with allexcept
Can someone explain me why these two formulas return different tables?
U Purchases = CALCULATE(COUNT('Sales Data'[MemberID]),'Sales Data'[Payment Date] >= DATEVALUE ( "30 August 2018" ),FILTER(ALLEXCEPT('Sales Data','Sales Data'[Title (groups)]),'Sales Data'[Title (groups)] = "U 1"),'Sales Data'[MemberID]=EARLIER('Sales Data'[MemberID]),'Sales Data'[Payment Date]<=EARLIER('Sales Data'[Payment Date]))
U Purchases = CALCULATE(COUNT('Sales Data'[MemberID]),'Sales Data'[Payment Date] >= DATEVALUE ( "30 August 2018" ),FILTER('Sales Data','Sales Data'[Title (groups)] = "U 1"),'Sales Data'[MemberID]=EARLIER('Sales Data'[MemberID]),'Sales Data'[Payment Date]<=EARLIER('Sales Data'[Payment Date]))The second one has extra rows for users that have bought U 1 and other products. Why?
Also this one:
U Purchases = CALCULATE(COUNT('Sales Data'[MemberID]),'Sales Data'[Payment Date] >= DATEVALUE ( "30 August 2018" ),'Sales Data'[Title (groups)] = "U 1",'Sales Data'[MemberID]=EARLIER('Sales Data'[MemberID]),'Sales Data'[Payment Date]<=EARLIER('Sales Data'[Payment Date]))Just sets all U purchases to 1 (instead of increasing a counter for each repeat sale) but it does not increase the counter when users bought U 1 and other products like the second one.
Hi Anonymous,
Below is the result in my test.
ALLEXCEPT removes all context filters in the table except filters that have been applied to the specified columns. Per my understanding, using ALLEXCEPT in your formula makes the calculation work on group level, while the calculation works on the whole table level without ALLEXCEPT. Here is a similar thread for your reference, but its a scenario about measure:
Best regards,
Yuliana Gu
4 Replies
Very difficult to tell exactly, sample source data that exhibited the issue would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Those are some pretty nastly filter statements and not sure why you are using a FILTER function in the middle there when you are already in a filter clause with your CALCULATE.
I started using Power BI 3 months ago so I am still learning how to use DAX effectively.
Especially filters they can be pretty confusing. The reason I use it is that it does what I want. I don't know why it works and that's what I am trying to figure out. I was just trying different things and that worked so that's why I use it.
Basically U Purchases is a calculated column that has a counter that increases each time a customer buys the same U 1 product. For example the first time is 1, second 2, thrid 3, etc. If it is not a U 1 product the respective row is left blank.
Unfortunately it is company data so I cannot post them without heavy masking which I don't have the time to do right now.
Well, basically, the FILTER with the ALLEXCEPT is going to essentially wipe out any other filters other than the Title group filter, the column specified in the ALLEXCEPT. But how that actually interacts with the other filters is going to matter on the order DAX applies those filters, which I'm not 100% certain I can answer that. Perhaps marcorusso?
Hi Anonymous,
Below is the result in my test.
ALLEXCEPT removes all context filters in the table except filters that have been applied to the specified columns. Per my understanding, using ALLEXCEPT in your formula makes the calculation work on group level, while the calculation works on the whole table level without ALLEXCEPT. Here is a similar thread for your reference, but its a scenario about measure:
Best regards,
Yuliana Gu
