How to Filter Measure in Power BI?

This Power BI tutorial will show how to filter measure in Power BI.

Recently, I worked on a Power BI report where I utilized a measure to filter the data essential for the report.

In this tutorial, I will tell you how to filter measures in Power BI with the help of two different scenarios.

How to Filter Measure in Power BI

Here, we will explore how to filter a measure in Power BI.

Example – 1 [Filter Measure in Power BI Slicer]

In this example, I will create a Power BI slicer, define a measure, and apply this measure in the filter pane. To do this, follow the below steps.

1. Open Power BI Desktop. Then go to Table view.

filter measure in power bi

2. Under the Home tab, click the New table.

how to use filter measure in power bi

3. In the formula bar, click the below expression. Then click the Commit button.

Year Table = GENERATESERIES(1980,2030,1)

Where:

  • Year Table = Name of the table
  • GENERATESERIES = DAX function, which returns a single-column table containing the values of an arithmetic series
  • 1980 = Start Value
  • 2030 = End value
  • 1 = Increment Value
how to filter a measure in power bi

4. After that, you will observe a list of the years created, with the column name displayed as “Value.” Double-click on the column and rename it to “Year.”

how to filter a measure in power bi dax

5. Click the Report view -> Click the Home tab -> expand Visual gallery(black box) -> Click the Slicer visual.

how to use measure in filter pane power bi

6. Then, using the +Add data option, add the Year into the Field

power bi filter measure by Year

7. Then, under the Home tab, click the New measure.

how to filter measure in power bi

8. In the formula bar, click the below expression. Then click the Commit button.

Filter Slicer = 
IF(
		VALUES('Year Table'[Year]) < YEAR(TODAY()) - 5 && VALUES('Year Table'[Year]) > YEAR(TODAY()) - 20,
		1,
		0
	)

Where:

  • Filter Slicer = Name of the measure
  • IF = DAX Function
  • VALUES = DAX Function
  • Year Table = Name of the table
  • Year = Name of the column
  • TODAY() = DAX Function, which returns today’s date
filter visual using measure power bi

9. Select the Slicer -> Expand the Filters panel. Then drag the Filter Slicer(Measure) into the Filter on this visual.

filters by measure power bi

10. In the Filters pane, select Show items when the value -> is -> 1. Then click Apply filter.

How to use Measure as a Filter in Power BI

11. After applying a filter on the slicer, it looks like the screenshot below.

How to Use Filter a Measure in Power BI

This way, you can use the filter as a measure in Power BI.

Example – 2 [Filter Measure in Power BI Table visual]

This example will explore filtering a measure within a Power BI table visual.

Here I have a SharePoint list (Super mart) that contains below columns with various data types:

ColumnData Types
RegionSinfle line of text
Product NameSingle line of text
SalesCurrency
how to filter measure in power bi dax

1. Open Power BI Desktop and load the data. Then, your data set will be shown in the Data pane.

power bi filter measure

2. Then go to Table view -> Under the Home tab, click the New table -> In the formula bar, click the below expression. Then click the Commit button.

New Sales = GENERATESERIES(10,2000,50)

Where:

  • New Sales = Name of the table
  • GENERATESERIES = DAX function
  • 10 = Start Value
  • 2000 = End value
  • 50 = Increment Value
how to filter measure values in power bi

3. Click the Report view -> Click the Home tab -> Expand Visual gallery(black box) -> Click the Slicer visual -> Then, using the +Add data option, add the Value into the Field.

how to create measure filter in power bi

3. Under the Home tab, Expand Visual gallery(black box) -> Click the Table visual -> Then, using the +Add data option, add the Product Name, Region, and Sales in the Column field.

how to use measure to filter in power bi

4. Then, under the Home tab, click the New measure.-> In the formula bar, click the below expression. Then click the Commit button.

Filter Sales = 
IF(
		SUM('Super mart'[Sales]) > SELECTEDVALUE('New Sales'[Value]),
		1,
		0
	)

Where:

  • Filter Sales = Name of the measure
  • IF = DAX Function
  • Sum = DAX Function
  • SELECTEDVALUE = DAX Function
  • Super mart, New Sale = Name of the table
  • Sales, Value = Name of the column
how to filter in a measure power bi

5. Select the Table -> Expand the Filters panel. Then drag the Filter Sales(Measure) into the Filter on this visual. Then, In the Filters pane, select Show items when the value -> is -> 1. Then click Apply filter.

how to use filter in power bi measure

6. Afterward, when you select any value in the slicer, the table visual will display the increased sales figures associated with that value.

How to Filter Measure in Power BI table visual

This way, you can use the filter as a measure in Power BI.

Conclusion

I hope this tutorial helps you to use the filter as a measure in Power BI.

In this tutorial, we covered using measures as filters in Power BI. Also, we see different examples, such as filtering measures using a Power BI slicer and a Power BI table visual.

Moreover, you may like some more Power BI tutorials: