Forum Discussion
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...
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"}
)