Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cmackie
Regular Visitor

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:

cmackie_0-1725582463761.png

 

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:

cmackie_3-1725583038178.png

 

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:

cmackie_2-1725582882044.png

 

Remove the status filter on the visual and you get the right answer:

cmackie_4-1725583137086.png

 

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.

6 REPLIES 6
Selva-Salimi
Solution Supplier
Solution Supplier

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.

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):

cmackie_3-1725583038178.png

 

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

PijushRoy
Super User
Super User

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




Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

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):

cmackie_0-1725620915208.png

cmackie_3-1725621019573.png

 

cmackie_2-1725620986441.png

 

Any other ideas?

 

Hi @cmackie 

Please share the DAX code




Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!





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

 

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.