Power BI Measure Filter

In this Power BI Tutorial, we will learn about the Power BI Measure filter. Also, we will discuss:

• Power BI Measure filter
• Power BI Measure filter multiple values
• Power BI Measure filter selected value
• Power BI Measure filter multiple columns
• Power BI Measure filter date
• Power BI Measure filter based on slicer selection
• Power BI Measure filter date range
• Power BI Measure filter based on another table

To execute all these above subtopics, here we are going to use sample data (excel file). Not only Excel files, but also we can import data from SharePoint list, txt file, My SQL database, CSV file, etc.

Power BI Measure Filter

In Power BI, we use a filter to reduce the row in a table, that we specified for calculations. A filter function returns a table that represents a subset of a specified table or expression.

We can not use Filter independently, so we have to embed in other expressions that require a table as an argument.

The syntax for the Filter function is:

Measure = FILTER(Table, FilterExpression)

For example, we will create a table(using the sample data) having profit according to region.

Here we will create a filter that will filter and calculate the sum according to our expression. Let’s create a measure that will show the filtered data except for the East region.

Measure = CALCULATE(SUM(Orders[Profit]),FILTER(Orders, Orders[Region]<> "East"))

We can see the table is filtered and it calculate the SUM according to our DAX expression.

This is how a Power BI Measure filter works.

Read How to use Power bi ribbon chart

Power BI Measure filter multiple values

Now we will see how to filter data using multiple values in Power BI. Here we are going to take the same table example that we previously created (table having Profit & region).

Then we will create a Power BI measure that will filter the table having data according to our condition.

For example, we will create a measure that filters the data only for the East or South region and calculate their sum.

Measure = CALCULATE(SUM(Orders[Profit]),FILTER(Orders, Orders[Region] ="East" || Orders[Region] = "South"))  

As we mentioned to filter the value for East or South from the whole region, they filtered and returned their SUM.

This is how we can calculate the Power BI Measure Filter with Multiple values.

Read Power bi measure divide

Power BI Measure filter selected value

In Power BI, we can filter a selected value using Power BI Mesure. Let’s have a look below example.

Here, we have created a table (using sample data) having columns such as product’s sub-category, their ship mode, and shipping cost.

Now we will create a measure to calculate the SUM of total shipping cost.

Total Shipping cost = SUM(Orders[Shipping Cost])

Again we will create another measure to filter the selected data and calculate from the whole. For this, the expression is:

Shipping Tax = 
IF(
   SELECTEDVALUE(Orders[Ship Mode]) = "Delivery Truck" , [Total Shipping cost] * 0.01
)

This measure will show us, if the ship mode is equal to ‘Deliver Truck’, then their tax will be multiplied by 0.01 to total shipping cost. The result will look like this:

The above table showing the calculated shipping cost only for selected data. This is how a Power BI Measure filter selected value works.

Read Power bi measure switch statement with examples

Power BI Measure filter multiple columns

Here we will see how a Power BI measure will filter multiple columns and return a satisfactory result to us.

To evaluate this, we are going to use the previous table that we have created using the sample data.

For example, if both columns have values such as ‘Office Machines'(in Product’s sub-category column) and ‘Delivery Truck'(in ship mode column), then it will return their total shipping cost.

Filtered Shipping cost = CALCULATE( 
    SUM(Orders[Shipping Cost]),
    'Orders'[Product Sub-Category] = "Office Machines" && 'Orders'[Ship Mode] = "Delivery Truck"
)

Here we will show this value by using a card chart:

It returned the value in SUM that contains both ‘Office Machines and Delivery Truck’. This is how we can filter the multiple columns by using Measure in Power BI.

Read Countif function in Power BI Measure + 10 Useful Examples

Power BI Measure filter date

Now we will see how to filter a table using Power BI Measure for date column. We are going to use the Sample data to apply the filter on that.

For example, it will create a table that having some specific dates from the start date to the end date which filters from the sample date.

Filter_Table = 
VAR DateStart =
    DATE ( "2015", "1", "5" )
VAR DateEnd =
    DATE ( "2015", "1", "10" )
RETURN
    CALCULATETABLE (
        Orders,
        FILTER ( Orders, Orders[Order Date] <= DateEnd && Orders[Order Date] >= DateStart )
    )

We can see, another table is created and with filtered data from 5th Jan 2015 to 10th Jan 2015. This is how we can use a Power BI Measure to filter a date field.

Read How to create a Measure based on Slicer in Power BI

Power BI Measure filter based on slicer selection

Follow this link to implement this.

Power BI Measure filter date range

Here we will see how to create a date range that will filter the data using a Power BI Measure.

To solve this, we have created two tables like this:

Table -1:

Table-2:

Now we will create a measure that will filter the data in table-2. For example, if we select a date option from table-1, then the data will filter and visualize in table-2 according to the date range.

IsSelectedPeriod = 
VAR __StartDate = MIN('Table-1'[From])
VAR __EndDate = MAX('Table-1'[To])
VAR __ProjectDate = MAX('Table-2'[Date])

RETURN
IF(
    __ProjectDate >= __StartDate
    && __ProjectDate <= __EndDate,
    1,
    0
)

For filtration, now we will drag this measure to the filter pane on Power BI Desktop.

When we select any date range from table-1, then it will show the filter data in table-2.

For example, if we will select the date range from 7/30/2021 to 8/5/2021, then the data will come like this:

This is how to filter date range using Power BI Measure.

Read Power BI Measure SUM

Power BI Measure filter based on another table

By following this step-by-step guide, we can see how to do filter in one table based on another table using Power BI.

Step-1:

Here we have created two simple tables to implement this.

Table-1: (Product)

Table-2: (Product details)

Step-2:

Now we will create a measure for the selected value of ID.

Selected Value for ID = SELECTEDVALUE('Product'[Product ID],0)

Step-3:

Again we will create another measure by using this measure to filter the data based on another table.

filter data = 
SEARCH(
    [Selected Value for ID],
    MAX('Product details'[ID]),,BLANK()
)

Now drag this measure to the Product details table.

Step-4:

Now we will convert the product table to a slicer visual. When we select any item from table-1(product), the data will be filtered in table-2 according to their ID number.

This is how to do filtering based on another table by using Power BI Measure.

You may like the following Power BI tutorials:

• Power bi slicer panel – How to use
• Power bi slicer filter another slicer
• Power bi treemap – How to use
• Power bi sort by measure
• Power BI Slicer Search Box
• Power BI Q&A – How to use with examples

Conclusion

From this Power BI Tutorial, we learned all about the Power BI Measure filter. Also, we discussed:

• What is BI Measure filter?
• How a Power BI Measure filter works with multiple values?
• How to works with Power BI Measure filter selected value?
• How to filter multiple columns using Power BI Measure?
• How to filter date column using Power BI Measure?
• How to work with Power BI Measure filter based on slicer selection?
• How to filter a date range using Power BI Measure?
• How to do filter based on another table by Power BI Measure?