Visual Representation of Filter Context in DAX Calculations

Introduction

• Goal: Provide a new perspective on filter context in DAX calculations
• Focus on visual representation and efficient DAX code writing
• Aim to understand and represent the target filter context visually

Demo Overview

Initial Setup

• Use classic Contoso database
• Report: Sales amount by product brand, product color, and customer country

Filter Context Analysis

• Each cell in the report has a unique filter context affecting the sales amount measure
• Example cell analysis: Sales amount for blue Contoso products in 2019, filtered by customer country
• Filters are tables: e.g., Product brand (Contoso), Product color (blue), Year (2019)

Slicer Example

• Slicer selections: Canada and the United States
• Filter context: Table with two rows (Canada, United States)
• Filters contain only unique values

Complex Filter Example

• Example of filtering by net price <= 10
• Filter context represented by tables with unique values that fit the condition (e.g., values that are < 10)

Manipulating Filter Context

Using CALCULATE and CALCULATETABLE Functions

• Goal: Plan DAX calculations by comparing initial and target filter contexts
• Example: Compute percentage of color
  • Division of current color value by total value (without color filter)
  • Removing color filter from the initial filter context using CALCULATE and REMOVEFILTERS

Example: Sales of a Single Brand

• Filter context: Initial filter context (color) and added filter (brand = Contoso)
• Result: Combined filters for color and brand

Complex Calculation Example

• Compare sales for each year after 2017 to 2017 as a benchmark
• Calculation involves replacing one existing filter with another
• CALCULATE adds/changes filters in the filter context

Special Cases

Using KEEP FILTERS Function

• Situation: Filter on existing filters (e.g., amount > 1000 within a filter pane filter)
• KEEP FILTERS prevents existing filters from being removed
• Ensures combination of existing and new filters

Conclusion

• Visual representation of the filter context as tables aids understanding and planning DAX calculations
• Filters can be added, removed, or replaced
• Use visualization to compare initial and target filter contexts

Enjoy DAX!