Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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"}
)
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
94 | |
90 | |
35 | |
35 |
User | Count |
---|---|
153 | |
99 | |
82 | |
63 | |
54 |