Power BI Filter Context

Power BI

When people hear “Power BI,” they often think of its stunning visuals and interactive dashboards. However, the real magic happens behind the scenes with Data Analysis Expressions (DAX). DAX is the powerful formula language that drives Power BI’s data transformation and analytical capabilities. One of the fundamental concepts of DAX is the filter context, which plays a crucial role in determining the data that appears in your visuals. In this article, I’ll explain what filter context is and how understanding it can help you unlock the full potential of your Power BI reports.

What is PowerBI Filter Context?

Filter Contexts are Filters generated from Powerbi Visuals and Slicers that allows you to break down and limit the calculation of measures to certain constraints, for example calculate the number of people who earn more than a 100k USD a year. (constraint)

Explanation Through Story Telling:

Timmy is fond of collecting marbles — red ones, blue ones, small ones, and big ones. Recently, he acquired his latest marble, an orange marble, which is the largest in his collection. Timmy’s net worth stands at an impressive 230 marbles. However, Timmy feels like his hobby is getting out of hand, as he can’t keep track of his collection anymore (you could say that Timmy is losing his marbles).

Timmy decided to create an excel sheet for his collection

Sample of Timmy`s marble collection

Timmy used his dataset to create a Powerbi Dashboard

creating a Powerbi measure

Timmy created 2 measures:

Measure to calculate the largest Marble:

Largest Marble = MAX(timmy_marbles[diameter])

Measure to calculate the Smallest Marble:

Smallest Marble = MIN(timmy_marbles[diameter])

Timmy used his measure to create a Table Visual

Powerbi marble`s data table visual

Unfortunately, Timmy was unsatisfied with the results as he wanted to know the largest and smallest marble diameter for each of his marble colors, but the visual just showed the largest and smallest diameter overall.

Here is where Filter Context comes into Play

Adding Filter Context to Powerbi Visual

By adding the color column to the table as a filter context, each row in the visual will create a filter that constraint the dataset on which we will calculate the measures specified (Largest Marble Diameter, Smallest Marble Diameter), where the constraint is:

• color = blue for the visual 1st row.
• color = green for the visual 2nd row.
• color = orange for the visual 3rd row.
• etc….

Interpretation in Pandas Python

For each color, the Pandas data frame will get filtered and then the Largest and Smallest Diameter values will be calculated and printed out.

import pandas as pd

colors = ["blue", "green", "orange", "red", "white", "yellow"]
for color in colors:
    pandas_dataframe = pd.read_csv("timmy_marbles.csv")
    filt = pandas_dataframe["color"] == color
    blue_marbles = pandas_dataframe.loc[filt]
    largest_marble = blue_marbles["diameter"].max()
    smallest_marble = blue_marbles["diameter"].min()

    print(color)
    print("Largest Marble Diameter:", largest_marble)
    print("Smallest Marble Diameter", smallest_marble)

Interpretation in SQL

It is the same as running this SQL query for each color in your dataset. (You can use group by color, but that’s for later)

select max(diameter) as "largest_marble", min(diameter) as "smallest marble" from timmy_marbles where color = "blue";

After suffering from insomnia for a couple of days, Timmy was finally able to grasp the concept of Filter Contexts in Power BI, and he was able to rest peacefully, knowing he could continue his marble collecting hobby.