ALL function

[pstanek]

hi Does function all remove  report level filter?

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

[KHorseman]

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.

[pstanek]

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

[KHorseman · 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"
	)
)