Reply
Frequent Visitor
Posts: 10
Registered: ‎10-18-2018
Accepted Solution

Difference between filter and filter with allexcept

[ Edited ]

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.


Accepted Solutions
Community Support Team
Posts: 5,440
Registered: ‎09-21-2016

Re: Difference between filter and filter with allexcept

Hi @Ipan,

 

Below is the result in my test.

1.PNG2.PNG

 

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:

Filter and Allexcept

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post


All Replies
Super User
Posts: 9,934
Registered: ‎07-11-2015

Re: Difference between filter and filter with allexcept

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Frequent Visitor
Posts: 10
Registered: ‎10-18-2018

Re: Difference between filter and filter with allexcept

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.

 

 

Highlighted
Super User
Posts: 9,934
Registered: ‎07-11-2015

Re: Difference between filter and filter with allexcept

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?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Posts: 5,440
Registered: ‎09-21-2016

Re: Difference between filter and filter with allexcept

Hi @Ipan,

 

Below is the result in my test.

1.PNG2.PNG

 

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:

Filter and Allexcept

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.