ALLEXCEPT Not Working With Filters On Visual

Question

I am having issues with getting the ALLEXCEPT function to ignore filters on a visual...

My model is as below:

Essentially what I am trying to achieve is to discover the percentage of declined proposals between a certain date and certain sales area(s) like this:

As you can see "Ignore Status" has a value of 17 (which I expect), however, when you try to replicate the same outcome on a visual the answer returned is 12:

Remove the status filter on the visual and you get the right answer:

I would have thought the measure would ignore the status filter against the visual but keep the proposal date filter.

Any ideas/thoughts are appreciated.

pijushroy · Answer

Hi&nbsp;cmackie&nbsp;Can you please try to remove one or more filters by using the REMOVEFILTER function in place of ALL EXCEPThttps://learn.microsoft.com/en-us/dax/removefilters-function-dax

cmackie · Answer

Hi&nbsp;PijushRoy&nbsp;&nbsp;Thank you for your quick response... However, unfortunately, still the same result (another proposal added since I posted taking from 17 to 18):&nbsp;&nbsp;Any other ideas?&nbsp;

selva-salimi · Answer

Hi&nbsp;cmackie&nbsp;,&nbsp;I think you should also add "status" in allexcept expression.&nbsp;&nbsp;If this post helps, then I would appreciate a thumbs up&nbsp;&nbsp;and mark it as the solution&nbsp;to help the other members find it more quickly.

pijushroy · Answer

Hi&nbsp;cmackie&nbsp;Please share the DAX code

cmackie · Answer

Hi&nbsp;PijushRoy&nbsp;&nbsp;Please see the link to a folder that contains:DAX Query (.dax)Dax Query (.txt)Data (.csv) to allow you to run DAX QueryALLEXCEPT Not Working With Filters On Visual&nbsp;If the link expires... Here is DAX query:&nbsp;DEFINE
	MEASURE 'Measure(s)'[CDD] = CALCULATE(COUNT('Proposal'[Proposal ID]),
                                            Proposal[Proposal Type]="New", 'Proposal'[Status]="Declined", 'Proposal Sales Area(s)'[Sales Area] IN {"Corporate","Corporate Bodyshop","Salary Sacrifice"})
	MEASURE 'Measure(s)'[CDA] = CALCULATE(COUNT('Proposal'[Proposal ID]),
                                            Proposal[Proposal Type]="New", 'Proposal Sales Area(s)'[Sales Area] IN {"Corporate","Corporate Bodyshop","Salary Sacrifice"},ALLEXCEPT(Proposal,Proposal[Proposal Date]))
	MEASURE 'Measure(s)'[CDAremovefilter] = CALCULATE(COUNT('Proposal'[Proposal ID]),
                                            Proposal[Proposal Type]="New", 'Proposal Sales Area(s)'[Sales Area] IN {"Corporate","Corporate Bodyshop","Salary Sacrifice"}, REMOVEFILTERS('Proposal'[Status]))

EVALUATE
	CALCULATETABLE(
		{
			("Deckuned Propsals",[CDD]),
			("All Proposals", CALCULATE([CDA],All('Proposal'))),
			("Ignore Proposal Date", CALCULATE([CDA],ALLEXCEPT(Proposal,Proposal[Status]))),
			("Ignore Status (AllEXCEPT)", CALCULATE([CDA])),
			("Ignore Status (REMOVEFILTER)", CALCULATE([CDAremovefilter])),
			("Ignore Status (REMOVEFILTER)", CALCULATE([CDAremovefilter]))
		},
		DATESBETWEEN('Proposal'[Proposal Date],"01/07/2024","30/06/2025"),
		Proposal[Status] = "Declined",
		'Proposal Sales Area(s)'[Sales Area] IN {"Corporate","Corporate Bodyshop","Salary Sacrifice"}
	)&nbsp;&nbsp;

Forum Discussion

ALLEXCEPT Not Working With Filters On Visual

6 Replies

Related content

Filtering with ALLEXCEPT()

Filter and Allexcept

Filter with ALLEXCEPT

ALLEXCEPT for multiple columns/Filters

FILTER ALLEXCEPT