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.

power bi measure filter
power bi measure filter

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"))
power bi measure filter
power bi measure filter

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).

See also  Power bi slicer filter another slicer - How to do

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"))  
power bi measure filter with multiple values
power bi measure filter with multiple values

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.

Power BI Measure filter selected value
Power BI Measure filter selected value

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:

Power BI Measure filter selected value
Power BI Measure filter selected value

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:

power bi measure filter multiple columns
power bi measure filter multiple columns

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.

See also  Power BI Bar Chart Rounded Corners

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 )
    )
power bi measure filter date
power bi measure filter date

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:

Measure Date Range in Power BI
Measure Date Range in Power BI

Table-2:

Measure Date Range in Power BI
Measure Date Range in Power BI

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
)
power bi measure filter date range 3
Power BI Measure filter Date Range

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

Date Range using Power BI Measure filter
Date Range using Power BI Measure filter

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

See also  How to Post an Adaptive Card to Microsoft Teams Using Power Automate?

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

Power BI Measure filter Date Range
Power BI Measure filter Date Range

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)

filter based on another table
filter based on another table

Table-2: (Product details)

filter based on another table
filter based on another table

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.

power bi measure filter based on another table
power bi measure filter based on another 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.

power bi measure filter based on another table
power bi measure filter based on another table

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

You may like the following Power BI tutorials:

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?
>