Do you want to filter your measures in Power BI?

Filtering measures in Power BI is an essential technique for refining and enhancing your data visualization and analysis. Measures, which are custom calculations derived from your data, provide valuable insights into your dataset.

However, to extract the most pertinent information, it’s crucial to master the art of measure filtering. Power BI offers a versatile range of options for filtering measures, enabling you to slice and dice your data in various ways to gain a deeper understanding of your business metrics.

In this post, you will explore the diverse methods and strategies for effectively filtering measures in Power BI, allowing you to unlock the full potential of your data and deliver more insightful and actionable reports.

Filter Measures by Categories

Power BI provides various options for filtering measures. One of the most straightforward approaches to applying a filter to a measure is by utilizing category-based filtering.

Once you have created a measure, all you need to do is effortlessly drag and drop the desired filter column onto the visual element.

Let’s assume you want to create a bar chart showcasing total sales by country, these are the steps you need to follow:

1. In the Visualizations pane, choose the bar chart option.
2. Drag and drop the filter column onto the Y-axis well.
3. Add the measure to the X-axis well.

The visual will subsequently showcase the sales amount attributed to each country. The column used to filter categories can also be based on a date. The crucial factor is that it contains categorical data sourced from a dimensions table.

Filter with Measure using the Filters Pane

The Filters pane in Power BI serves as a dynamic control center for refining and fine-tuning your data visualizations. This tool allows you to interact with data in a highly flexible manner.

With the Filters pane, you can easily apply filters to limit data based on specific criteria, ranging from date ranges to product categories, and instantly observe the impact of these filters on your reports and dashboards.

The filter’s pane provides three filtering options – Filters on this visual, Filters on this page, and Filters on all pages.

• Filters on this visual allow you to apply filters only to the currently active or selected visual.
• Filters on this page allow you to apply a general filter to every visual on the selected page.
• Filters on all pages allow you to apply a general filter on every page in the document.

The options for filters will vary based on the specific type needed. For example, suppose you want to display sales that surpass a particular threshold. In that case, you’ll need to apply a filter to the [Total Sales] measure.

Since numerical data is involved, the available options for filters will differ from those applicable to categorical data.

Here’s how you can add a filter to the [Total Sales] measure using the Filters on this visual option.

1. Click on the caret icon in the well containing the measure.
2. Click on the box containing the is less than condition. This will reveal a list of other conditions. Choose the one you want to use.

3. Enter the benchmark value into the provided text box, then click on Apply filter. If you need to utilize multiple benchmark values, you can employ the And or “Or conditions to allow the filter to determine how to combine the values.

Upon selecting the Apply filter condition, the visual display is updated to reflect the inclusion of the new filter condition. The visual indicates that there are three countries in which sales have reached or exceeded $3,000,000.

If you want to filter based on the categories, the options for this vary.

If you’re looking to add extra filters to the SalesTerritoryCountry category, you’ll find three options to choose from in the Filter type dropdown. The Basic filtering option lets you select pre-existing categories to filter by.

On the other hand, the Advanced filtering option allows you to apply various criteria like contains, is blank, and is empty for filtering purposes. Feel free to experiment with these filtering configurations.

Now, let’s explore the Top N option. With Top N, you can easily identify the highest-ranking categories based on a specific value. This feature provides valuable insights into your data.

This table presents the value of products sold exclusively in the United States. The complete matrix showcases the value of every product sold across all countries. The extensive amount of information on the table can be overwhelming and bewildering.

By applying the Top N filter, you can streamline this table and focus solely on the products with the most impressive sales rankings in each country.

Here’s how you can do it:

After selecting the Top N filter type, select Top and enter the number of ranks you want to see in the box available in the Show items section.

To input numeric values, go to the By value section and simply drag and drop the desired measure or column. Afterwards, click on Apply filter to apply the filter.

The table now appears less overwhelming, allowing you to concentrate effortlessly on the crucial information – a key benefit of utilizing the Top N filter.

Moreover, this feature saves you valuable time by eliminating the need to craft intricate DAX expressions to retrieve the highest-ranked values. Similarly, you can apply this approach to showcase the lowest-ranked values as well.

Filter with DAX Expressions

DAX expressions are an additional option for applying filters to a measure. This method offers great flexibility and complexity, allowing you to manipulate and control the filters used by measures to evaluate their results.

Understanding three key concepts – the filter context, row contexts, and context transition – is crucial for comprehending how DAX filters measures in different scenarios. By grasping the interplay of these concepts, you’ll gain a comprehensive understanding of measure behavior.

Row Context

Row context in DAX is the current row in a table that is being evaluated. For example, if you are writing a DAX measure to calculate the average sales per customer, the row context would be the current customer being evaluated.

Functions that operate within the row context and evaluate outcomes based on it are commonly known as iterators. These powerful functions iterate through each row in a column to derive the desired results.

Notable examples of these iterators include SUMX, MAXX, MINX, and FILTER.

Total Sales = 
SUMX(
    FactInternetSales,
    FactInternetSales[UnitPrice] * FactInternetSales[OrderQuantity]
)

This is an example of a measure that evaluates using based on a row context. In this DAX expression, values in the FactInternetSales[UnitPrice] and FactInternetSales[UnitPrice] are multiplied and summed up row after row.

Filter Context

Filter context in DAX is the subset of rows in a table that are currently visible. This subset is determined by the filters that have been applied to the table, both explicitly and implicitly.

Explicit filters are filters that you apply to a table using the Power BI interface, such as slicers and filter panes. Implicit filters are filters that typically operate internally within the DAX engines, without explicit definition in visuals or DAX statements. They function behind the scenes, subtly affecting data retrieval and analysis processes.

The CALCULATE and CALCULATETABLE functions are well-known for their ability to manipulate the filter context. These functions are responsible for modifying the filter context of measures, ultimately shaping the outcome.

Context Transition

Context transition refers to the process of converting the row context into a filter context. This conversion occurs automatically when using the CALCULATE function within a row context.

It’s important to note that a CALCULATE function doesn’t always need explicit inclusion in a DAX statement for the context transition to take place. The DAX engines utilize an internal CALCULATE function to evaluate measures, even when measures are not explicitly created with the function.

In essence, every measure inherently incorporates a CALCULATE function, regardless of whether it’s explicitly used or not. This process of context transition explains why measures are influenced by the filter context.

When context transition occurs, the CALCULATE function creates a new filter context that includes all the columns currently being iterated by the row context and filters them with the value of the current row. This mechanism ensures that measures respond to the specific filtering applied.

Understanding how context transition operates helps you to better comprehend the dynamic relationship between row and filter contexts, enabling you to construct more powerful and flexible calculations.

TotalSales = 
    SUMX(
        VALUES(
            DimSalesTerritory[SalesTerritoryCountry]
        ),
        CALCULATE(
            SUMX(
                FactInternetSales,
                FactInternetSales[OrderQuantity] *
                FactInternetSales[SalesAmount]
            )
        )
    )

This expression exemplifies how a context transition operates.

By using VALUES(DimSalesTerritory[SalesTerritoryCountry]), you can iterate over the specified column and generate a table consisting of unique values in a single column. This effectively introduces a row context.

The CALCULATE expression introduces the context transition which transforms the row context of the VALUE expression into a filter context.

What this means is that the CALCULATE expression will turn the values in the column returned by the VALUE expression into filters.

As such, the measure when added to a visual will return the total sales value for the current filter context or each country.

TotalSales = 
    SUMX(
        VALUES(
            DimSalesTerritory[SalesTerritoryCountry]
        ),
       [Total Sales]
    )

Here’s an alternative expression to represent the same concept. Both syntaxes yield identical results.

This is because measures, as previously mentioned, are executed using CALCULATE by the DAX engine. Consequently, they seamlessly execute context transitions when invoked within a row context.

TotalSales2 = 
    SUMX(
        VALUES(
            DimSalesTerritory[SalesTerritoryCountry]
        ),
        SUMX(
            FactInternetSales,
            FactInternetSales[OrderQuantity] *
            FactInternetSales[SalesAmount]
            )
        )

Now, what happens when you run the syntax without the CALCULATE as is in the above expression?

Upon the visual, the changes may not appear significant until reaching the Total row, where the discrepancy in values becomes apparent.

The variation in total values while maintaining similar values for each country can be attributed to the interplay of filters and row context.

By visualizing both measures in a card, the disparity in their total value becomes more apparent.

The discrepancy arises from the fact that the second measure ([TotalSales2]) calculates its total using a row context, whereas the first measure ([TotalSales]) derives its total through the introduction of a filter context by the CALCULATE function.

What’s happening in the TotalSales2 function is that the VALUE function returns a column with seven rows.

The SUMX expression then runs the same calculation for all seven rows without taking into account the values in each row because there’s no context transition.

If you multiply the TotalSales value by seven, you get the value returned by the TotalSales2 measure. This explains how the measure uses the row context to arrive at the total.

However, when this measure is filtered by the countries, it returns the correct values. That’s because of the external filter context introduced in the matrix by the [SalesTerritoryCountry] column.

In this case, the VALUE function returns one row, and the SUMX expression returns the value for the currently selected countries. Since there’s only one row, it iterates just once and returns the appropriate result.

That’s how the measure returns the correct total for each country. The value you see in the Total row represents the sum when there’s an absence of any external filter.

TotalSales3 = 
    SUMX(
        ALL(
            DimSalesTerritory[SalesTerritoryCountry]
        ),
        SUMX(
            FactInternetSales,
            FactInternetSales[OrderQuantity] *
            FactInternetSales[SalesAmount]
            )
        )

Here’s another modification of the total sales DAX expression, but this time using the ALL functioninstead of the VALUE function.

The ALL function removes every filter applied to the table, therefore any filter introduced in a visual will not affect the table.

As you can observe in this visual, the values for each country still vary, but they do not align with the values returned in the [TotalSales2] measure. The reason behind this discrepancy lies in the behaviour of the ALL function versus the SUMX function.

While the ALL function disregards any filters applied, the SUMX function does not. Consequently, the ALL function consistently returns seven rows in all cases.

On the other hand, the SUMX function calculates the total for the currently active filter, which is then evaluated for all seven rows returned by the ALL function.

By multiplying the actual values retrieved by the [TotalSales] measure for each country by seven, we can obtain the corresponding value for each country in the [TotalSales3] measure.

TotalSales4 = 
    SUMX(
        ALL(
            DimSalesTerritory[SalesTerritoryCountry]
        ),
        CALCULATE(
            SUMX(
                FactInternetSales,
                FactInternetSales[OrderQuantity] *
                FactInternetSales[SalesAmount]
            )
        )
    )

When a CALCULATE is introduced to the measure, it causes the measure to return an entirely different set of values. This syntax will return the same values for all the countries irrespective of the filter being applied.

This occurs because the CALCULATE expression evaluates within the filter introduced by the ALL function.

While the CALCULATE introduces the filter context, transforming the values in each row of the ALL table into a filter, the ALL function, on the other hand, hinders the filters by ignoring them.

As a result, the CALCULATE returns the same value for every row in the table.

This is visible when you add the measure to the matrix. If the filter comes from another column, then the CALCULATE will return values for each filter.

Here’s an alternative way to write the expression. The syntax simply instructs to calculate the total sales for each country, disregarding any filters applied to the DimSalesTerritory[SalesTerritoryCountry] column.

Having a solid understanding of filter and row contexts, as well as context transitions, makes it easy to create measures and control the filters they employ.

Moreover, comprehending how the CALCULATE function evaluates greatly aids in manipulating the filter context of a measure to achieve the desired outcomes.

Conclusions

Creating measures is the cornerstone of effective data analysis in Power BI because it allows you to gain insights from different angles. The three primary methods for filtering measures have been discussed in this post.

The techniques for filtering measures can vary in complexity, depending on the level of detail needed to examine your data. However, by grasping the fundamentals, you can create and filter measures to meet your specific requirements.