DAX functions are thriving nowadays; they have become one of the most popular and widely learned languages in the Business Intelligence (BI) domain. With Microsoft Power BI adoption accelerating globally, DAX expressions stand out as a powerful way to unlock meaningful insights from raw data.
In this blog, we’ll provide an overview of three of the most commonly used DAX expressions in Power BI: ALL, ALLSELECTED, and ALLEXCEPT. These functions are essential for creating accurate measures, dynamic reports, and enterprise-grade BI dashboards.
Looking to get hands-on guidance with DAX and Power BI? Talk to a Power BI Expert at Addend Analytics.
ALL – Reset Filters for a Complete View
The ALL function returns all the rows in a table or all the values in a column, ignoring any filters that might have been applied. This is especially useful when you want to calculate totals and percentages without being impacted by the current filter context.
Syntax:
ALL([<TableNameOrColumnName>] [, <ColumnName> [, <ColumnName> [, …]]])
This syntax helps us get a total sales value while ignoring any filters applied to the product name.
In the image shown below, we have a measure named “ALL” present in the table, which provides the total sales value unaffected by the product name filter.
Use Case: Businesses often use ALL in scenarios like calculating % of total sales, % contribution by category, or benchmarking KPIs across regions.
ALLSELECTED – Respect Explicit Filters, Ignore Row/Column Filters
The ALLSELECTED function returns all rows in a table or all values in a column, ignoring filters applied on rows or columns but respecting explicit filters applied by the user.
Syntax:
ALLSELECTED([<tableName> | <columnName> [, <columnName> [, <columnName> [,]]]])
This syntax helps us calculate sales value considering explicit filters but ignoring filters applied on rows or columns.
- Case A: The table shows that the “ALLSELECTED” measure total sales is not affected by the product name column present in the table.
- Case B: The table shows that only the “Product Name” explicit filter applied in the ALLSELECTED measure affects the total.
Use Case: ALLSELECTED is powerful in dynamic dashboards, where stakeholders apply slicers/filters like time periods or regions, but still need control over how totals aggregate.
Want to master advanced DAX for executive dashboards? Know more about the Power BI Training Session.
ALLEXCEPT – Keep Only Specific Filters Active
The ALLEXCEPT function returns all rows in a table or all values in a column, ignoring all context filters but keeping those for specified columns.
Syntax:
ALLEXCEPT(<table>, <column> [, <column> [,]])
This helps calculate sales value while considering explicit filters applied for a specified column (like “Product Name”), but ignoring others.
- Case A: The table shows that the “Product Name” filter does not affect the ALLEXCEPT total sales.
- Case B: The table shows that only the “Category” explicit filter affects the ALLEXCEPT total.
Use Case: ALLEXCEPT is often applied in scenario analysis where businesses want to preserve certain key filters (e.g., “Region” or “Product Category”) while ignoring others. This is critical for financial reporting, retail category analysis, or manufacturing performance dashboards.
Why These DAX Functions Matter for Business
- ALL, ALLSELECTED, and ALLEXCEPT are at the core of data modeling in Power BI.
- They ensure accurate totals, percentages, and KPIs for operational and executive dashboards.
- Businesses across industries, retail, manufacturing, finance, logistics, and healthcare — use these functions to unlock self-service analytics and AI-powered insights.
When implemented correctly, these functions not only improve report accuracy but also enhance decision-making speed, a key advantage in digital transformation.
Need help implementing enterprise-grade BI dashboards? Schedule a Free Power BI Consultation with Addend Analytics.
Understanding the nuances of ALL, ALLSELECTED, and ALLEXCEPT is crucial for any Power BI developer or business analyst. Each function has its place:
- ALL → Resets filters for complete totals.
- ALLSELECTED → Respects explicit filters, ignores row/column filters.
- ALLEXCEPT → Ignores all filters except those specified.
When combined, these functions form the foundation for scalable, reliable, and insightful reporting. Whether you’re building dashboards for sales performance, financial reporting, or supply chain monitoring, mastering these DAX functions is a must.
Addend Analytics, a Microsoft Solutions Partner, has delivered 100+ successful Power BI projects across industries. From Business Central integration with Power BI to Microsoft Fabric consulting, our team ensures your business gets the most value out of your data. Looking for DAX Training? Talk to Our Experts.
