Using ALLEXCEPT or ALL/VALUES?

on YouTube ↗22m

" summary

Demonstrates that ALL/ALLEXCEPT vs VALUES+REMOVEFILTERS is a functional choice in DAX: ALL or REMOVEFILTERS clear filters, ALLEXCEPT removes every other column but only works if the higher-level column (continent) stays in the visual. Builds a percentage-of-continent measure in Power BI using CALCULATE, VALUES and REMOVEFILTERS, tests it across drilldowns and when users remove continent or add product columns, and shows the CALCULATE+VALUES then REMOVEFILTERS pattern reliably preserves the intended continent value while ALLEXCEPT can fail.

AI-written summary · about

" chapters (9)

1. 0:00ALL and ALL/EXCEPT in DAX
2. 1:10ALL vs ALL/EXCEPT basics
3. 4:00Percentage of continent measure
4. 8:12ALL/EXCEPT for dynamic hierarchies
5. 11:28Testing ALL/EXCEPT with non-hierarchies
6. 13:38ALL/EXCEPT limitations
7. 16:07ALL/EXCEPT failures and alternatives
8. 17:35REMOVEFILTERS and VALUES pattern
9. 20:47Conclusion

" related talks

• 59mMastering Time-Based Analysis in DAXDan Clark · PASS Business Analytics Conference 2013
• 48mCombine Power BI with External ToolsMiguel Félix · SQLBits 2023
• 51mData modeling for experts with Power BIMarc Lelijveld, Jeroen ter Heerdt · SQLBits 2023
• 59mSelf-Service Data ModelingMarco Russo · PASS Business Analytics Conference 2013
• 53mHailing Frequencies: Analysis Services Terms and ConceptsMark Vaillancourt · PASS Business Analytics Conference 2013
• 58mProductivity Revolution in ExcelAvi Singh, Purna Duggirala · PASS BA Marathon 2015
• 30mData Skills for Data PeopleMiranda Osterheld, Azar Rahimi Dehaghani, Filippos Lymperopoulos, Dorian Scheidt, Ryan Atallah · Tableau Conference 2020
• 20mLet's Improve the Tabular Model by Combining QueriesAna María Bisbé York · Power BI Women Summit 2021