The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
hi Does function all remove report level filter?
Do I Have to use allexcept to preserve that filter in measure and calculated columns?
Solved! Go to 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" ) )
Proud to be a Super User!
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.
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" ) )
Proud to be a Super User!