ALLEXCEPT Not Working With Filters On Visual

[cmackie]

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.

[Selva-Salimi]

Hi @cmackie ,

I think you should also add "status" in allexcept expression. 

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.

[cmackie]

Hi @Selva-Salimi 

I do not think this is the case as you'll see from the screenshot that adding in "status" to the allexcept expression results in 201 (see Ignore proposal date):

As you can see, the "Ignore Proposal Date" essentially has "Status" and "Proposal Date" in the allexcept albeit a bit disjointed and messy.

[PijushRoy]

Hi @cmackie 

Can you please try to remove one or more filters by using the REMOVEFILTER function in place of ALL EXCEPT
https://learn.microsoft.com/en-us/dax/removefilters-function-dax

[cmackie]

Hi @PijushRoy 

Thank you for your quick response... However, unfortunately, still the same result (another proposal added since I posted taking from 17 to 18):

Any other ideas?

[PijushRoy]

Hi @cmackie 

Please share the DAX code

[cmackie]

Hi @PijushRoy 

Please see the link to a folder that contains:

• DAX Query (.dax)
• Dax Query (.txt)
• Data (.csv) to allow you to run DAX Query

ALLEXCEPT Not Working With Filters On Visual

If the link expires... Here is DAX query:

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"}
	)