Filtering measures through slicers

The video tackles a common Power BI question: can a slicer filter a measure? The immediate answer is no, because measures, unlike columns, cannot be directly filtered. Filters in Power BI apply to the values of specific columns when a measure meets a certain condition, not the measure itself. For example, you can filter products whose sales are greater than a certain amount, but not filter a 'sales' measure directly. This often leads to confusion.

The video explains that while Power BI's UI allows measure filters on visuals like a matrix, this doesn't mean the measure itself is being filtered. Instead, it filters the rows (e.g., categories or brands) based on the measure's value at that specific granularity. The presenter demonstrates how changing the granularity (from category to brand) affects the total in the filtered matrix, proving that the filter is applied to the column defining the granularity, not the measure.

The video then introduces a method to effectively 'filter a measure' using a slicer, but only after defining the granularity. This is achieved using a calculation group in Tabular Editor. A custom function `local filter products based on measure` is created, which takes a measure, a filter limit, and applies a filter with a predefined granularity (e.g., 'product'). This allows users to select thresholds via a slicer and see results filtered at a product level, independent of the matrix's rows.

For more advanced scenarios, the video shows how to dynamically choose the filter granularity (e.g., product, customer, or store) using a separate, disconnected table and another calculation group. A new function `local filter table` is introduced. This function reads the selected granularity from a slicer (populated by the disconnected table) and then uses a switch statement to apply the filter based on the chosen table (product, customer, or store). This offers a highly flexible way to filter data based on measure values and user-selected granularity.

The video concludes by reiterating that while a direct 'no' often answers the question of filtering measures with slicers, a deeper understanding reveals various techniques to achieve this by defining filter granularity. This approach not only solves the user's intent but also educates on the fundamental differences between columns and measures in filtering contexts within Power BI, highlighting the power of calculation groups for advanced filtering scenarios.