Director Accounting Insights Ltd.
Share this content

Power BI basics: An introduction to the filter context

5th Jul 2019
Director Accounting Insights Ltd.
Share this content
BI
iStock_LaurenceDutton_AW

A good understanding of Power BI’s filter context, and how to manipulate it, is essential if you want to create date-driven calculations such as year-to-date, year-on-year or month-on-month comparisons. In this article, AccountingWEB’s Power BI guru offers a primer on getting it right.

A basic difference between formulae in Excel and Power BI

In the Excel cell A3 in the image below, you can determine the result of the formula by reading it and looking at the values in cells A1 and A2.

 

You can see directly that the value in cell A3 is 750. It always will be, as long as you do not change the formula nor the values in cells A1 and A2. Power BI is not like that. In Power BI, you cannot generally determine the result of a formula just by reading it. Consider the image below.

 

In this simple Power BI report, I have just one table, called ‘TransactionLines’. It is a transaction listing from Sage 50 and contains all of the line items of all transactions for the past two years. In all, 57,491 transactions. 

There is a column called TransactionLines[NetAmount] that holds the net amount for each transaction. It has a default summarization setting “Sum”, which is the equivalent of applying the following calculation in the form of a measure

NetAmountDisplayed := SUM ( TransactionLines[NetAmount] ) 

Unlike in Excel, just by looking at the formula you cannot determine what value a visualization will display for [NetAmountDisplayed].

The result of any Power BI formula depends on its context 

In the report above, I have four visualizations that are displaying TransactionLines[NetAmount] 110 times: 

Visualization 

# times TransactionLines[NetAmount] is displayed 

Matrix (13 columns x 7 rows) 

91 

Tree Map (6 departments) 

Column Chart (12 months) 

12 

Card 

Total 

110 

The implicit measure [NetAmountDisplayed] runs 110 times to display 91 different values (6 departments + all departments) x (12 months + all months). Each of the 91 different results comes from a different set of input values for TransactionLines[NetAmount]. Different inputs into the same formula will produce different answers.

Welcome to Power BI’s “filter context”

Before Power BI performs any calculation, it applies a filter context, which reduces the dataset visible to the calculation. Depending on the nature of the calculation, it may also apply a “row context” as it iterates over each row of a column. The combination of the filter context and the row context gives an overall “evaluation context” for the calculation.

This article, however, is just an introduction to the filter context and our example illustrates just the filter context and in a very simple way. Consider the image below. 

 

Look at the number outlined in blue showing the sales for November 2018 in the Hair Salon department. £124,711.59 is the result of applying our implicit measure [NetAmountDisplayed], after applying all of the filters highlighted in red. That is:

Column 

Filter 

Effect 

TransactionLines[Type]  

= “SI”, “SC” or “SD” 

Reduces the number of rows visible in ‘TransactionLines’ to 35,129 out of 57,491. 

TransactionLines[Date].[Year]  

= 2018 

Further reduces the number of rows visible to 16,934 

TransactionLines[Date].[Month]  

= November 

Further reduces the number of rows to 1,401 

TransactionLines[DepartmentName]  

= “Hair Salon” 

Further reduces the visible rows to just 611 

It does not matter in what order these filters are applied, but once Power BI has applied all of the filters and in this case reduced the number of rows visible to 611, it performs the calculation of our implicit measure:

NetAmountDisplayed := SUM ( TransactionLines[NetAmount] ) to give a value of £124,711.59. 

This process is done for each of the 110 instances in the report page that display TransactionLines[NetAmount]. 

Therefore, to render the report page, Power BI applies 91 distinct filter contexts to 110 calculations, thus giving 91 different results for [NetAmountDisplayed].

Power BI visualizations calculate totals and subtotals separately, with their own filter context 

In the matrix visualization in the bottom left of the report, Power BI calculates the totals for each row and column separately, by applying a different filter context. It does not just sum all the values in each row and column. Look at the image below. Here I have changed the summarization setting for TransactionLines[NetAmount] from Sum to Average. 

 

This changes our implicit measure to the following: 

NetAmountDisplayed := AVERAGE ( TransactionLines[NetAmount] ) 

The value for the January total in the matrix is now £187.05. 

 

It is not the total for each of the values for each department, which is £755.04. Nor is it displaying the average of all of these values, which is £125.84. 

 

The “Total” of £187.05 that Power BI is displaying is the result of the following formula: 

NetAmountDisplayed := AVERAGE ( TransactionLines[NetAmount] ) 

With the following filter context applied: 

Column 

Filter 

TransactionLines[Type] 

“SI”, “SC” or “SD” 

TransactionLines[Date].[Year] 

2018 

TransactionLines[Date].[Month] 

January 

In other words, the “Total” of £187.05 is the average value of TransactionLines[NetAmount] for all sales receivable line items for January 2018. Power BI computes every displayed value independently, including totals and subtotals.

This behaviour is essential when working with averages or ratios, or indeed anything, that is not a simple sum. It also offers the possibility independently to manipulate the formula or format of any totals, which is something that you may well want to do if you want your report to look a certain way.

Important things that I have not discussed in this article 

This is just an introduction to what can be quite a large topic. Other things also come into play that I have not discussed here.

The overall “evaluation context”
In parallel with the filter context, which filters columns down to a subset of rows, there is also a “row context”. This identifies which row of a column that a calculation is looking at as it iterates over the filtered column. Together, the row context and the filter context form an overall “evaluation context” for a calculation. 

Relationships between tables
Table relationships influence the filter context. A normal one-to-many relationship from, for example, ‘NominalRecords’ to ‘TransactionLines’ will filter ‘TransactionLines’ by selected rows from ‘NominalRecords’. You will use this feature, for example, to create a visual to display a Profit & Loss statement. 

The filter context inside your DAX formulae
A filter context may also be present inside any formula that you write, not just in measures, but also calculated columns and calculated tables. In the simple example here, we were not writing any formulae, but just letting each visualization apply an implicit measure to the column TransacationLines[NetAmount] in our model. 

The CALCULATE function
The CALCULATE function is worth a special mention since when you use CALCULATE inside a formula, it directly modifies the filter context. CALCULATE is an extremely powerful function and will behave in subtly different ways, depending on whether you use it in a measure or a calculated column. You will use CALCULATE very often if you are analysing accounting data in Power BI, for example, if you want to select one month and display values based on the previous month, or year-to-date. 

When Power BI applies filter contexts 
In our simple example, Power BI was only applying any filter contexts at the time that any visualization needed to compute a value to display. This is not the only time that Power BI may apply a filter context.

For example, Power BI computes a calculated column at the time that you load your model. In this case, it will apply any relevant filter context based on your formula in order to compute all of the values of the column. Once created, a calculated column will behave just like any other column (e.g. TransactionLines[NetAmount]) in your report and may have a further filter context applied when you use this column in a visualization.

Summary 

This all sounds very complex. It is not really. I would rather use the adjective “powerful”. The mathematics behind any calculations that you may do as an accountant is generally very simple. 

They are mostly addition (with a bit of subtraction and the occasional multiply or divide). The power you need is to be able to apply relatively simple calculations to selected subsets of your accounting data.

If you are able to manipulate the evaluation context, and in particular the filter context for any formula, then you will be able to throw the right inputs into your calculation and produce YTD calculations, year-on-year, rolling 12 months and a host of other very useful things for your financial reports.

Useful links 

DAX function reference 

A beginner’s guide to measures vs calculated columns 

Using CALCULATE to modify the Filter Context 

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.