March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
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.
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.
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
Proud to be a Super User! | |
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?
Hi @cmackie
Please share the DAX code
Proud to be a Super User! | |
Hi @PijushRoy
Please see the link to a folder that contains:
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"}
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
123 | |
85 | |
85 | |
70 | |
51 |
User | Count |
---|---|
205 | |
153 | |
97 | |
79 | |
69 |