Forum Discussion

cmackie's avatar
cmackie
Regular Visitor
1 year ago

ALLEXCEPT Not Working With Filters On Visual

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 de...
Need Help
cmackie's avatar
cmackie
Regular Visitor
1 year ago

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's avatar
PijushRoy
Community Champion
1 year ago

Hi cmackie 

Please share the DAX code

  • cmackie's avatar
    cmackie
    Regular Visitor
    1 year ago

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