Power BI Filter Context

What is Power BI 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

Timmy used his dataset to create a Powerbi Dashboard

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

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

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
pandas_dataframe = pd.read_csv("timmy_marbles.csv")
colors = ["blue", "green", "orange", "red", "white", "yellow"]

for color in colors:
    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.