DAX Functions - All vs Allselected (Which one do you use?)

In this video, Mitchell Pearson explains the key differences between the ALL function and the ALLSELECTED function in DAX (Data Analysis Expressions), highlighting when and why you should use each one. The video provides a detailed comparison, focusing on how these functions behave differently when applied to Power BI reports.

What is the ALL Function?

• The ALL function removes all filters from a specific column or table, regardless of where those filters are applied (visuals, slicers, filters, etc.).
• This function is commonly used when you need to get a measure that represents the "total" or "all values" without any filtering context.
• For example, using ALL to calculate a "percent of total" measure, the ALL function removes all filters and provides a grand total, regardless of any slicers or filters in the report.

When to Use the ALL Function?

• Use the ALL function when you need to calculate values based on the entire data set, ignoring any filters applied in the report.
• This is useful for calculating totals, averages, and percentages when you don’t want the context of filters to affect the measure.

What is the ALLSELECTED Function?

• The ALLSELECTED function is similar to ALL, but it offers more flexibility. It removes filters applied at the visual level (e.g., rows or slicers) while respecting filters applied externally (e.g., from report filters or other visual elements).
• This means that the ALLSELECTED function allows for more specific control over which filters to ignore and which to respect, making it highly useful for more granular calculations.
• In the video, Pearson demonstrates how to use ALLSELECTED to calculate a measure that reflects the total sales for a selected set of countries, while still respecting any global filters applied outside the immediate visual.

When to Use the ALLSELECTED Function?

• ALLSELECTED should be used when you want to ignore filters within the immediate visual (like those applied in rows or columns) but still respect any filters that apply globally, such as report-level filters.
• This function is particularly helpful when creating more complex reports that need a balance between respecting some filters but ignoring others.

Key Differences Between ALL and ALLSELECTED

• ALL: Removes all filters across the table or column, providing a grand total regardless of the filters in the report.
• ALLSELECTED: Removes filters from the visual or immediate context but respects filters from the outer context or report-level filters.
• ALLSELECTED gives more flexibility for controlling what filters are ignored, making it a more versatile option in complex reporting scenarios.

Practical Example

In the video, Pearson demonstrates the application of both functions to calculate a "percent of total" measure in Power BI. He uses the ALL function to calculate the grand total sales and then modifies the calculation with ALLSELECTED to respect outer report filters while ignoring visual-level filters.

This allows for greater control over how the calculation behaves in different contexts and visuals, providing a dynamic and customizable approach to data analysis.

Conclusion

Mitchell Pearson’s video clearly explains the differences between the ALL and ALLSELECTED functions in DAX and when each function should be used. Understanding the nuances between these functions is essential for anyone working with DAX in Power BI, especially when building more advanced, dynamic reports.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on DAX and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.