Summary
In this video, the speaker discusses the fundamental differences between two DAX functions, "KEEP FILTERS" and "ALL SELECTED," illustrated within the context of a Contoso sales report. Using practical examples, the video clarifies how each function influences the filter context when calculating measures. The speaker highlights that while both functions can yield the same initial results, they often display divergent outcomes depending on existing selections in slicers and the specific filtering requirements of analyses. Ultimately, the insights provided help viewers understand when to appropriately use each function to achieve desired results in DAX.
Key Points
Introduction to DAX Functions
[00:00] The speaker introduces the topic, emphasizing the need to understand the differences between DAX functions "KEEP FILTERS" and "ALL SELECTED." They note that confusion arises because both functions can appear similar under specific conditions but yield different results based on filtering contexts.
[00:42] An example is set up using the Contoso dataset, where the speaker has created two measures: “trendy colors” which computes sales from selected trendy colors for the year.
Application of KEEPFILTERS vs. ALL SELECTED
[01:08] The measure "trendy colors 2" applies the filter using "KEEP FILTERS" to set product colors of red, green, and blue. This filter operates by retaining any existing filters while adding new conditions, thereby affecting the analysis context.
[02:04] When the "KEEP FILTERS" function is temporarily disabled, it is demonstrated that the output of "trendy colors 2" is larger because it disregards selections made in the color slicer, which impacts the final calculation of sales values.
Differences in Behavior
[02:26] The video transitions to describe the implications of using "ALL SELECTED," noting that this function creates an explicit filter by iterating through the list of colors visible outside the specific visual context. Hence, it retrieves selections that include only the values that are currently visible.
[03:52] A clarification is made that while "ALL SELECTED" might initially seem to align with the selections in the slicer, it consistently overrides existing filters when applied, leading to potentially misleading results.
Change in Results with Filtering
[04:32] The speaker illustrates, through a drill-down example, how "trendy colors 1" and "trendy colors 2" yield different values depending on the selected colors. The results indicate that "ALL SELECTED" adheres to whatever selections were made outside of the matrix, while "KEEP FILTERS" only considers those elements that intersect between the applied and existing filters.
[05:19] Further examination showcases instances where selections can lead to blank outputs, emphasizing that the output of "KEEP FILTERS" can change based on the visibility of selected elements, as it only includes values that are matched in both the slicer and defined measures.
Visual Explanation and Conclusion
[06:35] The speaker outlines the mechanics of DAX functions more abstractly using a whiteboard, illustrating how "ALL SELECTED" captures visible elements in a slicer and includes them in calculations, whereas "KEEP FILTERS" seeks to combine existing filters without removing them.
[08:09] A final recap solidifies the core distinction: while "ALL SELECTED" removes existing selections in favor of new filters, "KEEP FILTERS" retains the previous context, allowing for a more inclusive operational view.
[09:17] The video concludes by reiterating the importance of context in DAX measures, encouraging viewers to use the appropriate DAX function based on whether they want to preserve existing filters or start fresh with their own selections. The speaker expresses hope that viewers found clarification in distinguishing between "ALL SELECTED" and "KEEP FILTERS" as vital tools in DAX calculations.
Live Report
Codes used
Trendy Colors 1
Trendy Colors 2
Questions ?
The following tips could give you some upper hand in debugging the DAX measures related to KEEPFILTERS & ALLSELECTED.
When using KEEPFILTERS it acts as an intersect operator.If anything common then it displayed up.Otherwise blank.
ALLSELECTED on the other hand as a starting point respects the outer filter selections.But when it coupled with CALCULATE and FILTER modifier what ever the values that have been selected already, will be overwritten by calculate.But within the scope of already selected items(This is tricky-see Trendy colours 01 )