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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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