Every visualization in Power BI has a Visual Level Filters section that by default includes all the columns and measures included in the visualization. This section can include additional filters over columns and measures. The Visual Level Filters section is the only one accepting a measure as a filter, whereas Page Level Filters and Report Level Filters only accept columns as a filter.
A measure used as a filter requires a target for the filter itself. A visual has an implicit definition of the target of the filter made by the columns (or by a subset of the columns) used in the visual. The user interface of Power BI does not feature a specific tool to specify a target for the filters defined at the page and report levels. This is the reason why measures can only be used as filters in visual level filters.
In Power BI it is possible to create a measure filter working at a granularity that is different from the one shown in a report by the visual. However, this technique gets different results depending on the visualizations used in the report, and it can have unexpected or counter-intuitive side effects in the measures used in the report. The goal of this article is to explain how to use a measure as a filter in a Power BI visualization, controlling the granularity and the possible side effects, all the while writing measures that will provide the correct results and avoiding unexpected behaviors.
How to apply a visual level filter in Power BI
Consider a simple data model reporting revenue by date. A matrix without any filter shows the following data.
In the Visual Level Filters section, the Revenues measure can have a filter as in the following example that requires Revenues to be greater than 10.
As one might expect, by applying this filter the visualization reduces the rows displayed and filters out the months between January 2018 and October 2018. Indeed, those months display a Revenues value under or equal to 10.
The matrix visual applies a measure filter at the cardinality defined by the rows of the matrix itself. In the previous example, the cardinality is year and month.
This was intuitive because both year and month were displayed in the visual. However, if one drills up the matrix to only show the year level, the filter is still applied to both the year and month columns, evaluating the Revenues measure for every month before applying the filter condition. This is clear when navigating from one level to another. However, looking at the report in a static way it might be counter-intuitive.
Moreover, only the Rows section of the matrix visual defines the granularity of a visual filter based on a measure. In fact, by moving the Month attribute to the columns section of the matrix, the result is different and all the months since January 2018 are visible.
The reason is that the visual filter now only has the Year as a target, because it is the only attribute applied to the Rows section of the matrix. Thus, the filter should now read as “only include years when Revenues is greater than 10”.
In fact, by increasing the filter to 100, the entire row for the year 2018 is removed from the visualization as shown in the following example.
A measure used in the visual filter is evaluated at a certain granularity defined by the visual. This evaluation generates the combination of values at the filter granularity that must be included in the visualization, even though the total is displayed at a different granularity. For a matrix visual, the filter granularity is defined by attributes applied to the Rows section of the matrix, ignoring the attributes applied to the Columns section.
Differences in measure filters between Power BI visuals
Power BI allows developers to easily change the visualization type. Because different visuals might have different criteria to establish the granularity for a measure filter, changing the visualization type with a measure filter active might provide unexpected results.
For example, consider the previous matrix with Year on rows, Month on columns, and a filter on Revenues that must be greater than 10. All the months were visible, and the filter was applied at the year level. By transforming that matrix into a Clustered Column Chart, the result no longer includes months between January 2018 and October 2018.
The measure is now evaluated by Year and Month, and not only by Year. The semantic is now, “only include months when Revenues is greater than 10”. However, we did not change anything in the properties. The Month attribute that was on the matrix columns is now in the chart legend.
Thus, a chart evaluates the measures used in visual filters at the granularity defined by combining the Axis and Legend sections of the chart. There are no differences between applying attributes only to Axis, or to Axis and Legend. The resulting granularity of the measures used in visual filters is the same and combines all the attributes. For example, the same filter is obtained by applying both the Year and the Month attributes to the Axis section and drilling up the chart so that only the year level is visible.
The Revenues measure is still evaluated for each year and month. In fact, the Revenues value for the year 2018 is 23, which only includes months with a value greater than 10 (November and December 2018).
Controlling measure filter granularity
By knowing that the cardinality of a measure filter can include attributes that are not visible in the report, it is possible to define a more complex filter logic without having to write any DAX code. For example, by applying the Year and Customer attributes in the matrix rows and the Month attribute in the matrix columns, the filter semantic is, “show customer Revenues greater than 9,999 in the year displayed”.
By drilling down the matrix rows including the customers, it is visible that the filter is applied at the year level. The three customers highlighted in red in the following screenshot (19042, 19083, and 19039) do not have any Revenues greater than 9,999 for any months. The filter is applied to the customers at the year level, though the evaluation of the measure in the matrix also includes the month level. Customer 19081 highlighted in purple has one month above 9,999 and two months below – this case will be useful later in the article.
By moving the Month filter in the matrix rows, the filter semantic changes to: “show customer revenues greater than 9,999 in a single month”.
The result of the matrix by year is now different; those months with a Revenues amount under or equal to 9,999 are now excluded by the filter. For example, compare the Revenues result for 2007 in the following report – the numbers are lower compared to the previous reports.
By drilling down at the customer level, it is clear that any combination of customer and month with Revenues less than 10,000 is not included. This is particularly evident in April and May 2007 where only one customer is included, as highlighted in red in the following screenshot. Moreover, if a customer has revenues greater than 9,999 in certain months, and other months that don’t go over the filter, only the revenues for the months greater than 9,999 are considered. Thus, the yearly amount of a customer only includes the months greater than 9,999. For example, in the following screenshot customer 19081 in purple has revenues only in March and not in February and April – which were displayed in the previous matrix for that same customer.
This technique is interesting to filter an entity that is not displayed in the report. The filters shown in this section of the article only includes customers that have a certain revenues amount. However, the granularity used to compute and apply the filter is controlled by other attributes included in the visualization. It is important to understand this logic to obtain the correct results.
Unexpected behavior of complex measures using measure filters
Combining a complex measure with a visual filter at a granularity that is different from the current visualization can have unexpected results. For example, we can replace the displayed measure with a year-to-date calculation (Revenues YTD) keeping the filter on Revenues.
If the filter were applied at the year level for every customer, one might expect the same result. However, the filter is applied to every combination of customer and month, resulting in a strange report.
The value of 2007 is different than any other 2007 total from the previous reports shown in this article. Moreover, the values displayed in April and May are smaller than March in 2007. For a year-to-date calculation, this seems wrong. However, if we think about the semantic, there is an explanation for this. The filter for April 2007 is “show the Revenues YTD measure for customers that have a Revenues amount in April 2007 greater than 9,999”. For this reason, customers displayed in one month could be ignored in following months.
For example, customer 19037 is shown in both February and March because it has more than 9,999 in Revenues for each of those two months. This is visible by drilling down the data at the Customer level including both Revenues and Revenues YTD in the visualization.
Also consider the case of customer 19081. Even though it is only displayed in March, their Revenues YTD value is larger than Revenues. This is because the Revenues YTD measure considers the sum of previous months, even though Revenues may be lower than the threshold of 9,999.
Because the filter granularity is Year-Month-Customer, only the filtered combinations are also considered in the year total. This explains another unexpected result. The Revenues YTD computed in December is different from the one computed for the entire year – yet another unexpected behavior for a year-to-date calculation. At the month level, only customers with Revenues higher than 9,999 in December are considered, including all the months in their Revenues YTD calculation. However at the year level, all customers with revenue higher than 9,999 in at least one month are considered; their revenues for the entire year are summed to compute Revenues YTD regardless of the monthly filter applied to the Revenues measure.
The total of Revenues YTD 2007 is smaller than the Revenues measure filtered by year. Indeed, it is excluding customers that have a Revenues value greater than 9,999 at the year level, without having any month over that same limit (see customers 19043, 19083, and 19039 already highlighted in one of the previous examples).
The effects of this discordance between measure filter, granularity and displayed values are made more evident by using a clustered column chart with the same granularity of Year and Month. In this chart, we display Revenues YTD and we filter the Revenues measure. Finally, we also include the customer in the filter granularity as a hidden column in the Axis section of the chart.
The numbers are identical to those displayed in the previous matrix – the problem is just more evident because of the graphical representation. One would expect a year-to-date measure to show continuous growth over the year, something that clearly is not happening here.
However, the problem is that the filter is applied at a granularity level not consistent with the expected behavior of the measure displayed, at the granularity displayed. In order to get the expected result – filtering the customer revenue by year and not by month – it is necessary to control the measure applied to the filter, so that the resulting filter does not interfere with the measure displayed.
Controlling side effects of measure filters in Power BI
The previous problem can be solved with two different approaches: using a specific measure as a filter in the report; or creating a measure that displays the value, applying the filter in DAX instead of relying on the hidden attributes to define the filter granularity in the report.
The first solution requires a measure created specifically to be a filter in the Visual Level Filters section. If the purpose is to filter those entities (e.g. customers) that have a certain Revenues amount for the entire year, the measure could simply ignore any value at the month level. For example, Revenues Year is a measure that computes the Revenues at the year level, regardless of the date granularity used in the current filter context.
Revenuess Year :=CALCULATE ( [Revenues], ALL ( 'Date' ), VALUES ( 'Date'[Calendar Year Number] ) )
This measure must be used in the Visual Level Filters section instead of Revenues, whereas Revenues YTD is still the measure displayed in the chart.
Using this configuration, both chart and matrix consider the same customers used by the first matrix shown in the “Controlling measure filter granularity” section.
The second solution requires a Revenues YTD Filtered measure that includes the filter over customers. The following implementation internally uses the same Revenues Year measure used as a filter in the previous solution.
Revenues YTD Filtered :=VAR FilterCustomers = FILTER ( Customer, [Revenues Year] > 9999 )RETURN CALCULATE ( [Revenues], DATESYTD ( 'Date'[Date] ), FilterCustomers )
By using the Revenues YTD Filtered measure in the chart Value section, the Visual Level Filters section is no longer used in the visual as shown in the following screenshot.
This moves the complexity over to just the DAX code. Furthermore, the resulting data model could be easier to use by users that only design the report for an existing model, without a deeper knowledge of the details about visual level filter granularity described in this article.
Power BI accepts measures as filters only at a single visual level, and it does not accept measures as filters at the page level nor at the report level. The granularity to which a measure filter is applied partially depends on the visual type: for example, there are differences between a matrix and a chart. The granularity of a measure filter can be controlled by using columns not displayed in the report, but this technique requires a deeper understanding of the possible side effects. In order to avoid unexpected results, one might prefer to write either specific measures to be used only as a filter at the visual level, or measures that embed the filter in the measure itself – rather than relying on standard measures used in both visualizations and filters.
Power BI accepts measures as filters only at a single visual level, and it does not accept measures as filters at the page level nor at the report level. The granularity to which a measure filter is applied partially depends on the visual type: for example, there are differences between a matrix and a chart.Can you use a measure in a slicer? ›
use "Status"[Status] to create a slicer. 3. create a measure, then drag it to the table visual of the original table and set the value as "1".How do you apply a FILTER in Power BI? ›
- Open the Visualizations and Filters pane and the Fields pane, if they're not already open.
- From the Fields pane, select the field you want to add as a new report-level filter, and drag it into the Report level filters area.
- Select the values you want to filter.
The SUMX function should be implemented through measure and not through column, otherwise, the same result will be obtained for each row-to-row combination. SUMX is different from SUM because the SUM function just adds all the column values.Can I use filter in SUMX? ›
SUMX calculates a sum over a table. The second part of the formula, FILTER(table, expression),tells SUMX which data to use. SUMX requires a table or an expression that results in a table. Here, instead of using all the data in a table, you use the FILTER function to specify which of the rows from the table are used.Which is better slicer or filter? ›
There is no difference between Slicer and Filter. Because Slicer visual is visible on the dashboard whereas Filter has the same effect by not applying the Slicer visual on the dashboard.Is a slicer the same as a filter? ›
Slicers are visualizations on a Power BI canvas that allows users to refine the data for themselves easily. Filters are for developers to refine specific visuals, entire pages, or whole workbooks before sharing the dashboard(s) with end-users.Can a calculated column used in a slicer? ›
Slicers cannot be used in calculated columns. They can, however, be used in measures. Slicers are Filters, and thus use filter context.Can we use measure in DAX? ›
Data Analysis Expressions (DAX) is a programming language that is used throughout Microsoft Power BI for creating calculated columns, measures, and custom tables. It is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values.What is the difference between DAX and measure? ›
Measures and calculated columns both use DAX expressions. The difference is the context of evaluation. A measure is evaluated in the context of the cell evaluated in a report or in a DAX query, whereas a calculated column is computed at the row level within the table it belongs to.
FILTER DAX Function (Filter)
Returns a table that has been filtered.
- Select any cell within the range.
- Select Data > Filter.
- Select the column header arrow .
- Select Text Filters or Number Filters, and then select a comparison, like Between.
- Enter the filter criteria and select OK.
Unlike SUM, SUMX is capable of performing row-by-row calculations and iterates through every row of a specified table to complete the calculation. SUMX then adds all the row-wise results of the iterations of the given expression.Can you use Rankx in measure? ›
The RANKX function in simple terms returns the ranking of a specific number in each row of a table that forms part of a list of numbers. The RANKX function may be used in both calculated columns as well as calculated measures.Can you reference a measure in a calculated column? ›
We can create a calculated column to reference a measure value like this: Column1= <measure name>. But you need to note the calculated column values are calculated based on table context so it's fixed. To do further calculation, you can use measure directly without creating additional calculated column.How do you apply a filter to a sum function? ›
Just organize your data in table (Ctrl + T) or filter the data the way you want by clicking the Filter button. After that, select the cell immediately below the column you want to total, and click the AutoSum button on the ribbon. A SUBTOTAL formula will be inserted, summing only the visible cells in the column.What is difference between filter and Keepfilters in DAX? ›
The net effect over any one column is that both sets of arguments apply: both the filter arguments used in CALCULATE and the filters in the arguments of the KEEPFILTER function. In other words, whereas CALCULATE filters replace the current context, KEEPFILTERS adds filters to the current context.How do I apply a filter to a specific column? ›
Filter for a specific number or a number range
Click a cell in the range or table that you want to filter. On the Data tab, click Filter. in the column that contains the content that you want to filter. Under Filter, click Choose One, and then enter your filter criteria.
These other articles explain how to make specific types of slicers: Numeric range slicers. Relative date slicers.
Slicers are a visual way to filter data in a pivot table. With the Report Connections icon, you can have a slicer control multiple pivot tables on a dashboard—but only if all the pivot tables are from the same data set.Can we add filter to slicer? ›
Create a slicer to filter data
In the Insert Slicers dialog box, select the check boxes for the fields you want to display, then select OK. A slicer will be created for every field that you selected. Clicking any of the slicer buttons will automatically apply that filter to the linked table or PivotTable.
Slicers are visual elements, so they enjoy the same freedom of movement as other visualizations on the report, while filters do not. This is at the same time a great advantage and disadvantage.What is the difference between a filter and a separator? ›
Filter vs Separator
Filters use most of the bowl for filtering whereas separators have extra space at the bottom of the bowl for more sand to collect. In applications with heavy sand or sediment that needs to be removed having the extra space means it does not need to be purged as often.
There are three types in which you can select the slicer items; single select, multi-select and select all option.Are measures faster than calculated columns? ›
Because measures are not stored in memory, they are generally faster, although it is important to recognize the tradeoff between utilizing in-memory storage or processing power in an instance where either a measure or a calculated column could be used.What is the difference between a calculated field and a calculated item? ›
The key difference between calculated fields and calculated items is that: Calculated Fields are formulas that can refer to other fields in the pivot table. Calculated Items are formulas that can refer to other items within a specific pivot field.Do slicers only work with tables? ›
Note: Slicers are only available in Excel 2010 or later and unfortunately only work with Tables in Excel 2013, but don't let that stop you cause you can still take advantage of Tables and use Slicers with PivotTables in Excel 2010. Enter your email address below to download the sample workbook.How do you query a measure in DAX? ›
MEASURE DAX Statement
The MEASURE keyword introduces a measure definition (DEFINE) in a query (also known as query measure). The syntax after MEASURE defines a measure, which can be consumed in any other expression of the same query. A query measure overrides a model measure with the same name.
This is because measures only use CPU, whereas calculated columns use space on both disk and RAM. Thus, it is more space and memory efficient to avoid using calculated columns.
By this article it seems that M is faster.What are the two types of DAX functions? ›
You can create two types of expressions or calculations using DAX in Power BI; calculated columns and calculated measures.Which is better m or DAX? ›
Though both languages have similar functionalities, they operate independently of each other. M is used to pre-process the data inside the Query Editor whereas DAX is used to analyse the data after the data is loaded into the Data View Model.Do you need FILTER in CALCULATE DAX? ›
The CALCULATE function used without filters achieves a specific requirement. It transitions row context to filter context. It's required when an expression (not a model measure) that summarizes model data needs to be evaluated in row context.What is difference between FILTER and CALCULATE in DAX? ›
The CALCULATE function accepts a table expression returned by the FILTER DAX function, which evaluates its filter expression for each row of the Product table. It achieves the correct result—the sales result for red products. However, it could be achieved much more efficiently by using a Boolean expression.How does FILTER function work in DAX? ›
The FILTER function in DAX allows you to iterate down the rows of any table, creating a row context for each and testing whether the row should be included in your calculation. You can combine filters using keywords like AND and OR and also nest one filter within another.What are the 3 types of filtering processes? ›
There are several filtration methods : simple or gravity, hot and vacuum filtrations. The selection of the appropriate method is typically dictated by the nature of the experimental situation.What are the three stages of filter? ›
There are three stages of filtration: mechanical, biological, and UV clarification.What are the three types of filter systems? ›
- Mechanical Filters.
- Absorption Filters.
- Sequestration Filters.
- Ion Exchange Filters.
- Reverse Osmosis Filters.
- Click the dataset in the Editor.
- Click Settings.
- Click + Add Filter.
- Add filters using the following options: Field: The field in your collection you want to filter by. Condition: The condition you want to apply. Value: The value you want the filter to match.
- Click Add Filter.
The filter() method creates a new array filled with elements that pass a test provided by a function. The filter() method does not execute the function for empty elements. The filter() method does not change the original array.
The ArrayFormula function outputs the values from cell ranges into multiple columns and rows. Unlike standard formulas, the function is expandable, so it iterates on new data instantly. You can also use ArrayFormula in conjunction with other functions, such as VLOOKUP, FILTER, IF, or SUMIF.Can measures be used as filters in tableau? ›
Tableau provides the ability to filter individual views or even entire data sources on dimensions, measures, or sets.Can you filter measure values in tableau? ›
Measure Values and Measure Names are in most respects like other fields in Tableau: You can display a filter for Measure Names. You can format Measure Values—the formatting is then inherited as the default formatting for all measures. You can drop Measure Values and Measure Names on shelves.Can you add a measure in power query? ›
From a table in the Fields pane, right-click or select More options (...), and then select New quick measure from the list. Under Calculations in the Home tab of the Power BI Desktop ribbon, select New Quick Measure.
Right-click on "Measure Names" in the Filter shelf and select Show Filter. Open the quick filter menu by clicking the arrow in the upper right corner and select the filter type of your choice.Are measures affected by filters? ›
Because in Power BI, measures are always affected by the filters coming through visuals (filter context), the ability to sometimes IGNORE these filters, can be very useful on many occasions.How do you filter multiple measures in Tableau? ›
- Go to analysis tab and create a parameter. ( below for example)
- Click on set from field, a set of data sources used are shown, select the source and then select the measure. the follow same procedure and select multiple measures.
- This way multiple measures can be used for the same parameter.
- Create a new worksheet Bar Chart.
- Drag Order Date to Columns.
- Drag Measure Names to Filters card, select Sales and Profit.
- Drag Measure Values to Rows.
- Drag Measure Names to the Color of Marks Card.
- Change the chart type to Bar.
In the Data window, right-click the relevant field. Select Default Properties > Sort. Change the default sort order of the field using the various options, and then click OK.
You need to go to: File > Options and then Settings > Options and settings > DirectQuery, then selecting the option *Allow unrestricted measures in DirectQuery mode**.How do I add quick measure? ›
To create a quick measure right-click on a field (any field) and select New quick measure. Then select the calculation you want to use and the fields against which you want the calculation to run.How do I add a filter to a calculated field? ›
- Go to the Breakdown worksheet. ...
- Create a calculated field with that field in the Filters pane as seen in Fig. ...
- Make a new worksheet and put the new calculated field [Title – Breakdown] onto the Text card in Marks pane. ...
- Make the desired formatting. ...
- Now, go to your dashboard.
However, if you've already added worksheets to the dashboards, you can add filters as follows. Select the worksheet on the dashboard. On the drop-down menu under Filters chose the field you would like to use as a filter.How do you add filters to data validation? ›
Creating the Drop Down Filter
Go to Data –> Data Validation. In Data Validation dialogue box, select the Settings tab. In Settings tab, select “List” in the drop down, and in 'Source' field, select the unique list of countries that we generated. Click OK.