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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
pstanek
Post Patron
Post Patron

ALL function

hi Does function all remove  report level filter?

 Do I Have to use allexcept to preserve that filter in measure and calculated columns?

1 ACCEPTED SOLUTION

Yes. Any filtering-type functions will return a single table. FILTER, SUMMARIZE, ALL, ALLEXCEPT, almost all of the time intelligence functions, ADDCOLUMNS, GENERATE, etc. etc. etc. If you're filtering two tables then you need two filtering functions, in this case two ALLEXCEPT functions.

 

The exception to this would be if you want to filter a single table based on a combination of conditions in that table and conditions in a related table. In that case you could nest ALLEXCEPT inside a FILTER and use RELATED to check a column in a related table. This only works in N:1 relationships, unless you want to get crazy with some combination of VALUES and RELATEDTABLE that I won't get into right now. Anyway, example:

 

Measure = CALCULATE(
	SUM(FactTable[NumberColumn]),
	FILTER(
		ALLEXCEPT(
			FactTable,
			FactTable[CategoryColumn]
		),
		FactTable[ConditionColumn] = "foo" &&
		RELATED(DimensionTable[StatusColumn]) = "xyzzy"
	)
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
KHorseman
Community Champion
Community Champion

Yes, and yes. ALL removes all filters including row context filters, visual level, page level, and report level filters. ALLEXCEPT(TableName, TableName[WhateverColumnIsInTheReportLevelFilter]) will preserve the report level filter but remove all other filters.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




So when I want to preserve filters from two tables, I have to use allexcept twice

Yes. Any filtering-type functions will return a single table. FILTER, SUMMARIZE, ALL, ALLEXCEPT, almost all of the time intelligence functions, ADDCOLUMNS, GENERATE, etc. etc. etc. If you're filtering two tables then you need two filtering functions, in this case two ALLEXCEPT functions.

 

The exception to this would be if you want to filter a single table based on a combination of conditions in that table and conditions in a related table. In that case you could nest ALLEXCEPT inside a FILTER and use RELATED to check a column in a related table. This only works in N:1 relationships, unless you want to get crazy with some combination of VALUES and RELATEDTABLE that I won't get into right now. Anyway, example:

 

Measure = CALCULATE(
	SUM(FactTable[NumberColumn]),
	FILTER(
		ALLEXCEPT(
			FactTable,
			FactTable[CategoryColumn]
		),
		FactTable[ConditionColumn] = "foo" &&
		RELATED(DimensionTable[StatusColumn]) = "xyzzy"
	)
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

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

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors