Month October 2022

Power BI Filter Context: Unraveling the Impact of Filters on Calculations

emguyant Leave a comment

Series Review

One of the early stages of creating any Power BI report is the development of the data model. The data model will consist of data tables, relationships, and calculations. There are two types of calculations: calculated columns, and measures.

Check out Power BI Row Context: Understanding the Power of Context in Calculations for key differences between calculated columns and measures.

Power BI Row Context: Understanding the Power of Context in Calculations

Row Context — What it is, When is it available, and its Implications

All expressions, either from a calculated column or a measure, get evaluated within the evaluation context. The evaluation context limits the values in the current scope when evaluating an expression. The filter context and/or the row context make up the evaluation context.

Power BI Row Context: Understanding the Power of Context in Calculations explores the row context in depth. While Power BI Iterators: Unleashing the Power of Iteration in Power BI Calculations explores iterator functions, which are functions that create row context.

Power BI Iterators: Unleashing the Power of Iteration in Power BI Calculations

Iterator Functions — What they are and What they do

This article is the third of a Power BI Fundamental series with a focus on the filter context. The example file used in this post is located here – GitHub.

Introduction to Filter Context

Filter context refers to the filters applied before evaluating an expression. This filter context limits the set of rows of a table available to the calculation. There are two types of filters to consider, the first is implicit filters or filters applied by the user via the report canvas. The second type is explicit filters which use functions such as CALCULATE() or CALCULATETABLE().

The applied filter context can contain one or many filters. When there are many filters the filter context will be the intersection of all the filters. When the filter context is empty all the data is used during the evaluation.

The filter context does not iterate — this is a key difference between the filter context and the row context

Filter context propagates through the data model relationships. When defining each model relationship the cross-filter direction is set. This setting determines the direction(s) the filters will propagate. The available cross-filter options depend on the cardinality type of the relationship. See available documentation for more information on Cross-filter Direction and Enabling Bidirectional Cross-filtering.

It is important to be familiar with certain DAX functions which can modify the filter context. Some examples used in the post include CALCULATE(), ALL(), and FILTER().

The CALCULATE Function

The CALCULATE() function can add filters to a measure expression, ignore filters applied to a table, or overwrite filters applied from within the report visuals. The CALCULATE() function is a powerful and important tool when updating or modifying the filter context.

The syntax of CALCULATE() is:

CALCULATE(<expression>, <filter1>, <filter2>, ...)

Use the CALCULATE() function when modifying the filter context of an expression that returns a scalar value. Use CALCULATETABLE() when modifying the filter context of an expression that returns a table.

Exploring Filter Context

The table below is a visualization of the total sales amount for each product color.

The table visual creates filter context, as seen by the total sales amount for each color or row. Evaluating SalesAmount2 occurs by first filtering the SalesOrderDetail table by the color, and then evaluating the measure with the filtered table. This is then repeated for each product color in the SalesOrderDetail table.

The above example only contained the single product color filter. However, as mentioned previously, the filter context can contain multiple filters. The example table below adds the ProductType to the table. The addition of this field breaks down the total sales first by color and then by product type. For each row, the underlying SalesOrderDetail table is first filtered by color and product type before evaluating the SalesAmount2 measure. In these examples, it is the table visual that is creating the filter context.

Create Filter Context with Slicers

Another way to create filter context is through the use of slicer visuals. For this example, a slicer of the ProductType is created.

When no value is selected in the slicer the filter context from the slicer visual is null. Meaning at first the card visual shows the SalesAmount2 value evaluated for all data. Additionally, when no value is selected in the slicer the only filter context is ProductColor from the table visual.

Following the selection of BK in the product type slicer, the values in both the table and the card visual are updated. The card visual now has one filter context which is the product type BK. This is evaluated by creating a filtered table and SalesAmount2 is evaluated for this filtered table.

The SalesAmount2 measure is defined by:

SalesAmount2 =
SUMX (
    SalesOrderDetail,
    SalesOrderDetail[OrderQty] * SalesOrderDetail[UnitPrice] * 
    ( 1 - SalesOrderDetail[UnitPriceDiscount] )
)

After selecting an option from the slicer the measure is re-evaluated. The re-evaluation occurs to account for the newly created filter context. The filter context creates a subset of the SalesOrderDetail table that matches the slicer selection. Then the row context evaluates the expression row-by-row for the filtered table and is summed. SUMX() is an example of an iterator function, see Power BI Iterators: Unleashing the Power of Iteration in Power BI Calculations for more details. The updated value is then displayed on the card visual.

Power BI Iterators: Unleashing the Power of Iteration in Power BI Calculations

Iterator Functions — What they are and What they do

The table visual works in a similar fashion but, there are two filters applied. The table visual has an initial filter context of the product color. After the selection of BK, the table gets updated to visualize the intersection of the product color filter and the product type filter.

Following a selection in the slicer visual, if a row in the table visual is selected this will also apply a filter. The filter context is the intersection of the table selection filters and the slicer. The updated filter context gets applied to all other visuals (e.g. the card visual).

The filter context can contain one or many filters. The filter can come from one or many visuals and gets applied before evaluating the expression. Meaning the filter context gets applied before using the row context to evaluate an expression row-by-row

Create Filter Context with CALCULATE

Previous examples created the filter context using implicit filters. Generally, the user creates this type of filter through the user interface. Another way to create filter context is by using explicit filters. Explicit filters get created through the use of functions such as CALCULATE(). For this example, rather than having to select BK in the slicer to view total bike sales, we will use CALCULATE(). We will create a new measure that will force the filter context. We can do this because CALCULATE() allows us to set the filter context for an expression.

We define the BikeSales measure as:

BikeSales =
CALCULATE ( 
   SalesOrderDetail[SalesAmount2], 
   Products[ProductType] = "BK" 
)
  • Expression: SalesOrderDetails[SalesAmount2]
  • Filter: Products[ProductType]="BK"

BikeSales is then added to the table visual alongside SalesAmount2. When the BK product type is the slicer selection the two table columns are equal. Both measures have the same filter context created by product color and product type. Removing the implicit product type filter by unselecting a product type updates the filter context. The SalesAmount2 expression is re-evaluated with the updated filter context. Since the filter context created by the slicer is now null the SalesAmount2 value calculates using all the data. The BikeSales values do not change. This is because of the explicit filter used by the CALCULATE() function when we defined the measure. The BikeSales measure still has the filter Products[ProductType]="BK" applied regardless of the product type slicer.

The CALCULATE() function only creates filter context and does not create row context. So an important question to ask is why or how the BikeSales measure works. The CALCULATE() function references a specific column value, Products[ProductType]="BK". Yet, the CALCULATE() function does not have row context. So how does Power BI know which row it is working with? The answer is that the CALCULATE() function applies the FILTER() function. And the FILTER function creates the row context required to evaluate the measure.

Within the CALCULATE() function the Products[ProductType]="BK filter is shorten syntax. The filter argument passed to CALCULATE() is equivalent to FILTER(ALL(Products[ProductType]), Products[ProductType]="BK")). The ALL() function removes any external filters on the ProductType column and is another example of a function that can modify the filter context.

Keep External filters with CALCULATE

The CALCULATE() function evaluates the filter context both outside of and within the function. The filter context outside of the function can come from user interaction with visuals. The filter context within the function is the filter expression(s).

CALCULATE() overwrites external filters or filters that are outside of the function

To explore this we create a table with the Product Type, SalesAmount2, and SalesBike.

The SalesAmount2 column shows the total sales amount, if any, as expected. While the BikeSales column shows the same repeated value for all rows and is incorrect. Looking at the Product Type BK row we can see this row is correct. This table demonstrates that CALCULATE()overwrites external filters.

For example, the BB product type row filters SalesOrderDetail before evaluating SalesAmount2. This returns the correct total sales for the BB product type. When evaluating BikeSales this external product type filter gets overwritten. The measure calculates the sales amount value for the BK product type due to the explicit filter and returns this value for all rows.

CALCULATE() can be modified to keep both external and internal filters

Using the KEEPFILTERS() function within CALCULATE() will force CALCULATE() to keep both external and internal filters.

To do this we update BikeSales to:

BikeSales = 
CALCULATE(
   SalesOrderDetail[SalesAmount2], 
   KEEPFILTERS(Products[ProductType]="BK")
)

After updating the measure definition the resulting table is shown below.

Keeping the external filters is shown by the empty values for all rows except BK. For example, we look again at the BB product type row. When evaluating BikeSales Power BI keeps the external filter Products[ProductType]="BB" and the internal filter Products[ProductType]="BK". When applying more than one filter the filter context is the intersection of the two. The intersection of the two applied filters for the BB row is empty. A product cannot be both of type BB and BK.

More CALCULATE Examples

The CALCULATE() function plays an integral part in the filter context. Below are more examples to show key concepts and show that CALCULATE() is an important part of the filter context.

Creating a measure of High Quantity Sales

For the first example, we will be creating a sales measure showing the total sales amount for high-quantity orders. Creating this measure requires first filtering the SalesOrderDetail table based on the OrderQty. Then evaluating the SalesAmount2 measure with this filtered table.

We define HighQtySales as:

HighQtySales = 
CALCULATE(
   [SalesAmount2], 
   SalesOrderDetail[OrderQty]>25
)

We then visualize this measure on a card visual and see that 96.30K of our total 109.85M sales come from a high-quantity order. This again demonstrates the filter arguments passed to CALCULATE() are shorthand syntax. The filter arguments within CALCULATE() use the FILTER() function to create the row context required. In this example SalesOrderDetail[OrderQty]>25 is equivalent to FILTER(ALL(SalesOrderDetail),SalesOrderDetail[OrderQty] > 25).

The FILTER() function is an example of an iterator function and creates the row context. The row context allows for row-by-row evaluation of the OrderQty. Meaning it evaluates SalesOrderDetail[OrderQty] > 25 for each row of the SalesOrderDetail table. FILTER() then returns a virtual tale that is a subset of the original and contains only orders with a quantity greater than 25.

The CALCULATE() function creates filter context because the filter arguments provide a table. This table is the intersection of all the filter expressions passed to CALCULATE().

Percentage of Sales by Product Color

For the second example, we will create a measure to show the percentage of total sales for each product color. To create this we will start with a new AllSales measure. AllSales uses the CALCULATE() function to remove any filters and evaluates SalesAmount2.

We define AllSales as:

AllSales = 
CALCULATE(
   [SalesAmount2], 
   ALL(Products[Color])
)

AllSales is then added to the table visual Percentage of Sales by Color. Once added the AllSales column shows 190.85M total sales value for each color. This is consistent with the SalesAmount2 card visual. Repeating this value for each color is also expected because of the filter expression ALL(Products[Color]).

ALL(Products[Color]) creates a new filter context and gets evaluated with any other filters from the visuals. In this example, CALCULATE() overwrites any external filters on Products[Color]. This is why once added to the table visual AllSales displays the total sales value repeated for each row.

The ALL() function removes any filter limiting the color column that may exist while evaluating AllSales. It is best practice to define a measure as specific as possible. Notice, in this example ALL() applies to Product[Color], rather than the entire Product table. If other filters exist on other columns from the visuals these filters will still impact the evaluation. For example, selecting a product type from the slicer will adjust all values.

Following the selection, SalesAmount2 represents the total BK sales for each color. While the AllSales measure now represents the total sales for all BK product types. This occurs because when there are multiple filters the result is the intersection of all the filters.

In this case, All(Product[Color]) removes the filter on the color column. The slicer visual creates an external filter context of only BK product types. During the evaluation, the intersection of these two creates the evaluation context.

We can also remove the external filter context created by the product type slicer. To do this, we update the AllSales measure to include Products[ProductType] as an additional filter argument.

We update the filter expression of the CALCULATE() function to:

AllSales = 
CALCULATE(
   [SalesAmount2], 
   ALL(Products[Color], 
   Products[ProductType]
)

After updating the measure the AllSales column of the table visual updates to the total sales value. The column now displays the expected 109.85M value and is no longer impacted by the filter context created by the slicer visual.

Another option to remove the filter context within CALCULATE() is to use the REMOVEFILTER() function.

AllSales = 
CALCULATE(
   SalesOrderDetail[SalesAmount2], 
   REMOVEFILTERS(
      Products[Color], 
      Products[ProductType]
   )
)

We created AllSales as an initial step of the broader goal to calculate the percentage of total sales. To calculate the percentage we will update the AllSales expression. We can do this by saving the AllSales expression as a variable within the measure. We will also create another variable to store the SalesAmount2 value, which will be the total sales for each product color. Lastly, we will update the measure name to PercentageSales which will RETURN the division of the two sales variables.

PercentageSales =
VAR Sales = SalesOrderDetail[SalesAmount2]
VAR AllSales = 
CALCULATE(
   SalesOrderDetail[SalesAmount2], 
   REMOVEFILTERS(
      Products[Color],
      Products[ProductType]
   )
)

RETURN
DIVIDE(Sales, AllSales)

It is best practice to use the DIVIDE function because it provides handling of divide by zero errors and will return a blank or a specified value.

Thank you for reading! Stay curious, and until next time, happy learning.

And, remember, as Albert Einstein once said, “Anyone who has never made a mistake has never tried anything new.” So, don’t be afraid of making mistakes, practice makes perfect. Continuously experiment and explore new DAX functions, and challenge yourself with real-world data scenarios.

If this sparked your curiosity, keep that spark alive and check back frequently. Better yet, be sure not to miss a post by subscribing! With each new post comes an opportunity to learn something new.

Power BI Iterators: Unleashing the Power of Iteration in Power BI Calculations

emguyant Leave a comment

Iterator Functions — What they are and What they do

Overview

In order to facilitate analysis and visualization in Power BI a data model must first be created. The data model consists of individual data tables, relationships, and calculations. Calculations come in the form of either calculated columns or measures.

Check out this post to explore the key differences between calculated columns and measures.

Power BI Row Context: Understanding the Power of Context in Calculations

Row Context — What it is, When is it available, and its Implications

The evaluation context limits the values in the current scope when Power BI evaluates a DAX expression. There are two types of evaluation context, filter and row, that can be active during the evaluation of a DAX expression.

This post is the second of a Power BI Fundamental series with a focus on iterator functions. The example file used in this post is located found on GitHub at the link below.

GitHub – Power BI Key Fundamentals

Power BI key fundamentals example files

Introduction

This post will build upon the Power BI file created in Part 1 of the series titled row_context_example. The example file for this post is iterator_functions, and both can be found on GitHub at the link above.

As noted at the end of Power BI Row Context: Understanding the Power of Context in Calculations, creating a measure by default was unable to reference the row values of a column. When creating a measure a column can be referenced and passed to a standard aggregation function. The standard aggregation function will return only a single aggregated value. This is not the row-by-row functionality that will be required to replace a calculated column, such as SalesAmount found in the SalesOrderDetail table.

Creating the desired measure will require an iterator function to create row context.

Understanding Iterators

An iterator moves row-by-row or iterates through an object. The object can be a data model table or a virtual/temporary table. Data model tables are tables loaded into or linked to within Power BI. A table generated from within a measure that persists only for a temporary period of time is a virtual table.

Since an iterator moves row-by-row it has row context, and row context always iterates

An iterator function can return a single value (e.g. number, text, date) or a virtual table. An iterator generally has two arguments:

  • The object (i.e. table) to iterate through
  • The expression evaluated for each row of the object

It can be helpful to think of the expression as a temporary column of the object. Evaluation of the expression occurs row-by-row creating a column of calculated results. The column of results only persists during the evaluation and is not loaded to the data model. The purpose of the temporary column is to calculate the final returned value of the iterator.

A common identifier of an iterator function is an X at the end of the function’s name

Examples of iterator functions include SUMX, MINX, MAXX, AVERAGEX, and RANKX. The ending X is only a common identifier, FILTER is an example of an iterator function that does not end with a X.

Example iterator function:

SUMX(
   SalesOrderDetail, 
   SalesOrderDetail[OrderQty] * SalesOrderDetail[UnitPrice] * (1 - SalesOrderDetail[UnitPriceDiscount])
)
  • Table: SalesOrderDetail – the object iterated over
  • Expression: SalesOrderDetail[OrderQty] * SalesOrderDetail[UnitPrice] * (1 - SalesOrderDetail[UnitPriceDiscount]) – the expression evaluated for each row.

Once implementing the iterator SUMX and specifying the table as SalesOrderDetails the columns of the table will be recognized and able to be referenced like when a calculated column was created. The new measure SalesAmount2 created using an iterator will replace the calculated column SalesAmount. Comparing the default aggregation of the SalesAmount column (i.e. Sum of SalesAmount) and the new measure SalesAmount2 it can been see the values are equal.

Iterator Functions that Generate Virtual Tables

There are iterator functions such as SUMX that return a scalar value and there are ones like FILTER that return virtual tables.

To further explore, first create a new table in the data model generated by the returned table of the FILTER function.

For this example, we use the FILTER function to create a new ProductBlue table. For the first argument of the FILTER function we pass in the Product table. Then we filter that table using a filter expression defined as Products[Color] = "blue".

ProductBlue = FILTER(Products, Products[Color] = "blue")

We then can visualize this new table and compare the row count to the count of ProductID by color of the original table. Comparing these two tables we see that ProductBlue is a subset of the Product table.

The generation of this table highlights the iterating functionality and the row context when evaluating the FILTER function. To create the ProductBlue table the FILTER iterator function must create row context. During the evaluation, the function must go within the Products table and for each row (i.e. row context) evaluate what the product color is. If the color is Blue the function returns True otherwise returns False. The resulting table then consists of only rows from the original table which evaluated to a value of True.

Creating the new ProductBlue table was for demonstrative purposes. The table returned by FILTER can be a virtual table used within a measure. In this case, the ProductBlue table would only persist while the measure is being evaluated. As an example, create a new ProductBlueMeasure measure using the COUNTROWS function. COUNTROWS takes a single argument, the table to count the row of. To do this we will pass the FILTER expression used to create the ProductBlue table to the COUNTROWS function. To further demonstrate that the ProductBlue table is unnecessary, we can create a similar ProductBlackMeasure by changing =”blue" to =”black". Viewing these measures shows the result is the same counts that were produced by the other methods used to obtain this count.

Combining Iterators

More complex measures can be created by combining or nesting iterators. For example, combining the product color and the sales data, such as evaluating the total sales for only blue products. This measure will first create a virtual table of the sales data for only blue products. Then iterate row-by-row through the virtual table and evaluate the sales amount. Finally, return the total sales amount.

The DAX expression will be:

SUMX(
   FILTER(
      SalesOrderDetail, 
      RELATED(Products[Color])="Blue"
   ), 
  SalesOrderDetail[OrderQty] * SalesOrderDetail[UnitPrice] * (1 - SalesOrderDetail[UnitPriceDiscount])
)

The virtual table mentioned above is generated by FILTER(SalesOrderDetail, RELATED(Products[Color])="Blue").
This is the first iterator function that is evaluated using the SalesOrderDetail table and the expression RELATED(Products[Color])="Blue".

RELATED() is a function that returns a related value from another table. This function can be used since the Product table is related to the SalesOrderDetail table with a key value of ProductID. The FILTER function then returns a subset of the SalesOrderDetail table containing only rows where the product is blue. This virtual table is then passed to SUMX.

Then the expression evaluated row-by-row is:

SalesOrderDetail[OrderQty] * SalesOrderDetail[UnitPrice] * (1 - SalesOrderDetail[UnitPriceDiscount])

Which returns the total sales amount for each row. Then this temporary column is summed by the SUMX function.

Viewing the two tables on the left, the totals sales for the Blue products for all methods is approximately 9.60M. Selecting the blue row in the top table filters the table below, to show sales by Product for only blue products. Viewing the totals for all three methods also shows a value of approximately 9.60M. Lastly, viewing the value card under Nested Iterator shows that the above created DAX expressions results in the same value of 9.60M.

Key concepts are highlighted in the above example

  • Both SUMX and FILTER create row context
  • The FILTER function returns a virtual table which is a subset of SalesOrderDetail
  • SUMX returns the sum of the row-by-row calculation of the sale amount
  • The table passed to SUMX is the table that is returned by the FILTER function, this table only persists during the evaluation of SUMX

As DAX expressions get longer and more complex formatting the expression will help make them easier to read and understand, tools such as DAXFormatter can aid in formatting

The concept of evaluation context has been mentioned in this post as well as in the previous post – Power BI Row Context: Understanding the Power of Context in Calculations.

Power BI Row Context: Understanding the Power of Context in Calculations

Row Context — What it is, When is it available, and its Implications

Evaluation context is the context in which a DAX formula evaluates a calculation. There are two types, Power BI Row Context: Understanding the Power of Context in Calculations explored the first type, row context. This post explored iterators or specific functions which create row context to perform multi-column calculations. Check out Part Three of this series which will explore the second type of evaluation context, the filter context.

Power BI Filter Context: Unraveling the Impact of Filters on Calculations

Filter Context – How to create it and its impact on measures

Thank you for reading! Stay curious, and until next time, happy learning.

And, remember, as Albert Einstein once said, “Anyone who has never made a mistake has never tried anything new.” So, don’t be afraid of making mistakes, practice makes perfect. Continuously experiment and explore new DAX functions, and challenge yourself with real-world data scenarios.

If this sparked your curiosity, keep that spark alive and check back frequently. Better yet, be sure not to miss a post by subscribing! With each new post comes an opportunity to learn something new.