How to create a filter in Power bi

In this power bi tutorial, we learned how to create filters in the power bi dashboard. And also we will discuss the below points:

What is filter in power bi
Types of filter in power bi
Types of filtering method in power bi
How to create filters in power bi dashboard
Power bi button to apply filter
Drill through filters in power bi
How to add date filters in power bi dashboard
Add date range filter in Power Bi
Create a filter measure in power bi
Custom filter in power bi
Power bi filter between two dates
Power bi filter multiple values
Power bi filter multiple conditions
Power bi filter between two tables
Power bi filter between two columns

Table of Contents

What is a filter in Power bi

In Power bi, filters are a powerful feature, used to filter data based on some selected condition.
In Power bi filter, we have to select some values within the fields column and refine the result based on that criteria only.

Types of filter in Power bi

Here we will discuss the different types of filters in power bi. These are

Visual level filter
Page level filter
Report level filter.
Drill through filter
Automatic filter
Cross-level filter

Visual level filter

In power bi visual level filter, only filters data for a given view wheather it is a table chart, matrix, card etc.
Visual level filter are the granular filter that you can use in your data, and they work with the content both the report level filter and page level filter.

Page-level filter

In power bi page level filter, filter the particular page of a given report, where a report made up of a collection of pages
this filters can be applied in power bi based on some condition on a particular page within a given report.
During a power bi report each page can have a special set of filter condition applied to.

Report level filter

In power bi the report level filter are the filter that you simply use to filter the entire report by the filter condition.
Report level filter in power bi are the ones that affect all data in this report.

Drill through filter

By making use of drill through filter in power bi, you will create a page that mainly focuses on the precise entities.
In power bi drill through filter get passed from one page to another via drill through feature.
In power bi there are two types drill through filter is there.
The first type of filter is one that invoke the drill through, that means you can edit, delete, clear, hide or lock this type of filter in power bi.
The second type is drillthrough filter, that gets passed to the target, based on the page level filter of the source page in power bi.

Automatic filter

In power bi automatic filter are added to the visual automatically, while building the power bi visual.
These power bi visual are made according to the field or the topic related to your visual.
While those users who have the permission to edit can further edit, rename, or sort the file according to thier preference.
This filter cannot be deleted in power bi, as there, as these are filter regarding the visual.

Cross Drill filter

In power bi, cross drill filter are automatically added to the pane, when drill down filter is passed to another visual on the power bi report page via cross- highlight feature.
Even if you edit a power bi report, you can delete, hide, clear, rename, lock or sort this filter because it is associated with the drill-through features of visual.
We can also edit cross drill filter because it comes from drilling down in another power bi visual.

Read Power BI Date Hierarchy

Types of filtering method in power bi

Here we will see different sorts of filtering methods in power bi desktop. These are basic filtering and advanced filter in power bi desktop.

Basic filtering

In power bi basic filtering, we have a scalable and searchable list, to seem for a worth and simply type within the search box.
Relying on the search terms you entered, the list of obtaining values is getting to be updated automatically.
Then we will select one or more listed items, using the white checkbox on the left of each entry.

Advanced filtering

The Advanced filter in power bi gives us the flexibility to extract your records to another location on the same datasets.
In this filter, you can use the OR and AND function to filter the data.

How to create filters in power bi dashboard

Here we will see how to filter the data in the power bi dashboard using the visual level filter in the power bi.

I have created a stacked column chart visualization, where I have added age in the axis field and income in the value field.

Now we will filter the stacked column chart by using the visual level filter.

In power bi desktop select the visual, it will active the filter pane.
Then under filter on the visual, you can filter the visual.
Expand the field/column, then select filter type as basic filtering, and also you can filter using advanced filtering and Top N.
Then you can see the list of data, select the data from the list, which will show the data in the visualization after filteration.

how to create filters in power bi dashboard — how to create filters in the power bi dashboard

In the below screenshot, you can see the power bi visual level filter on age, and also you can filter the income column.

Read Power bi group by date range

Power bi button to apply filter

Here we will see how to add a single apply button to the filter pane to apply all changes at once in the power bi desktop.

By apply all button feature, you can allow report consumer to apply many modification at once.
This feature can be helpful for power bi report authors who want to optimize the filter pane for query performance.
This capability, will be useful for any power bi report consumer, who does want to wait for the report to update each time a single variable is applies
By applying many power bi filter modification at once, the consumer spend less time for waiting to see the impact on the report visual.
By default this feature will be turned off and power bi turn it on or off at the power bi report level.

Let’s see how to enable the apply all features in the Power bi desktop filter pane.

In power bi desktop, click on the File -> option and setting -> Option.

Now option window will open, navigate to current file -> click on the Query reduction.
Under Filters, select the Add a single apply button to the filter pane to apply changes at once.
Then click on OK.

Now you can see the Apply button bottom of the filter pane. I have created a stacked column chart by using column income by the chart.
Then I apply filter based on the income column, then click on the Apply button.

In the below screenshot, you can see the filter visualization in the power bi desktop.

Read: Power BI Slicer between two dates

Drill through filters in power bi

Here we will see what is drilling through the filter and how to drill through the filter in power bi.

What is drill through the filter in power bi?

The drill through filter in power bi used to identify the columns that are used to drill through from one report page to another.
If the column are selected in power bi drill through the second report page only shows the selected reported data.

How do drill through the filter in the power bi?

Here I have created the two-page report on power bi desktop, I will filter the report based on gender by using drill through the filter.

In the visualization pane, under drill through drag and drop the gender field from the field pane.
Click on the drop-down and select used as category.

We have two option to filter the data based on gender i.e Female(F) and Male(M).
Here i have selected Female(F) from the option.

In the below screenshot you can see the power bi drill through filter the visualization.

Add date filters in Power bi

Here we will how to add a date filter in the Power bi dashboard. We will use the relative date filter to filter the visualization based on the date column.

By using the sample data I have created the below table visualization in power bi.

How to add date filters in power bi dashboard

Expand the date field in the filter pane, under filter type select Relative date.
And also you can use other sort of filter, for example Top N filter which will filter and show you Top n dates.
Then under Show items when the value, from the dropdown select is in the last, then write 50 days.
If your data have the todays date then you can select the Include today.
Click on Apply filter.

In the below screenshot, you can see the power bi date filter on the power bi dashboard.

Read Microsoft Power BI KPI Visual – How to use

Add date range filter in Power BI

Here we will see how to add a date range filter in Power bi. We will filter the date between the two ranges of the order date column in the sample data.

Open your Power Bi desktop.
Load the data using get data.
Now will create filter date range table, so, click on the Modelling tab -> New table.
Then write the below dax formula to filter the date between the two date range.

Filtered Orders = 
CALCULATETABLE(
    Orders,
    DATESBETWEEN ( Orders[Order Date], DATE(2016,1,15), DATE(2016,2,5) )
)

Now power bi will create a table with date range (2016,1,15) to (2016,2,5).
Next i created a table visual, i added order date column, category column and profit column from the Filtered orders table in the field pane.
In the below screenshot, you can see the table visualization having the data between the (2016,1,15) to (2016,2,5).

Create a filter measure in power bi

Here we will see how to create a measure using a different filter for two columns in power bi.

We will use the below sample table, which has three columns.

We will create a measure, which will retrives the sum of regular hours column and filter by the
- Contains only Direct in the change type column.
- Does not contain C in profit center.

Le’s see how to create a measure to filter for two columns in power bi.

To create a measure in power bi, click on the New measure from the ribbon in power bi desktop.
Then write the below measure to filter.

Measure1 = CALCULATE (
        SUM ( Table1[Regular hour] ),
        KEEPFILTERS ( Table1[Charge type] = "DIRECT" ),
        KEEPFILTERS ( Table1[Profit center] = "C" )
    )

In the below screenshot, you can see the Regular hour of charge type C and Direct.

Read Power bi slicer contains with examples

Custom filter in power bi

Here we will see how to build a custom filter in power bi. We will use the below sample table to build the custom filter in power bi.

We will use the manager column as a filter, when we will filter by selecting C it will filter, all the three entities and displayed, since C is the boss.
So first we will create a table, for slicer segment for manager column.
Then we will create a measure, which will filter according to the slicer table.

Let’s see how we will filter the table according to the manager column.

First, we will create a slicer table, click on the Modelling tab -> New table.
Then write the below Dax formula:

SlicerTable = SELECTCOLUMNS('ManagerTable',"Name",[Manager])

We will create a measure which will filter the table according to the manager column(slicer table).
To create the measure, click on the new measure from the power bi ribbon.
Then write the measure:

Measure2 = 

var _selectedvalue= SELECTEDVALUE(SlicerTable[Name])

return

IF(_selectedvalue= MAX('ManagerTable'[Boss]),MAX('ManagerTable'[Manager]),CALCULATE(MAX('ManagerTable'[Manager]),FILTER('ManagerTable','ManagerTable'[Manager]=_selectedvalue)))

Now we will create a table visual from the visual to see the filter result.
And also we will create a slicer which will help to filter the table visual.
Select the table visual from the visualization pane.
In the values field, drag and drop the Boss, name, Measure2 from the field pane

Next, we created a slicer visual from the visualization pane, then add the name column from the slicer table in the field pane.
In the below screenshot you can see when we click on the C in the slicer it filter the table visual.

Read Microsoft Power BI Card – How to use

Power bi filter between two dates

Here we will see how to calculate value between two dates in power bi. We will use the below sample tables to filter and calculate value between two dates.

Value table

Period Table

We will calculate the value( value table) between the start date and the end date. The result would look like this:

1/Feb/2021 – 28/Feb/2021 = 6
1/Mar/2021 – 31/Mar/2021 = 12

Let’s see how to calculate values between two dates.

Load the data using get data.
Then we will create a measure which will calculate the values between two dates.
So, click on the new measure from the ribbon in power bi desktop.
Then write the Dax formula:

Measure3 = 
CALCULATE (
    SUM ( ValueTable[Value] ),
    FILTER (
        ValueTable,
        ValueTable[Date] >= MIN( PeriodTable[Start Date] )
            && ValueTable[Date] <= MAX ( PeriodTable[End Date] )
    )
)

Then create a table visual, and drag and drop the Start Date column and End date column, and Measure3 in the value field.

Power bi filter multiple values

Here we will see how power bi filters multiple values by using slicer and filter in power bi desktop.

Here we are using the below sample tables, In the Fact table, one column contains no, one value, and multiple values are separated by ‘;’. slicer table contains only a single value

Fact Table

Slicer table

When we click on the A in the slicer, it should not only filter the rows that have A in the specified column but also A;C, A;B etc.

So first we will create a measure by using the selected value function.
Click on the new measure from the ribbon in power bi desktop.
Then write the measure:

Measure 2 = SELECTEDVALUE(Slice[Column 2])

Next we will create another measure, which we will use to filter the data.
Click on the new measure then click on new measure then write the measure:

check = 
IF (
    NOT ( ISERROR ( FIND ( [Measure 2], SELECTEDVALUE ( 'Fact table'[Column1] ) ) ) ),
    1,
    0
)

Create a table visual in the canvas, drag and drop the column 1 in the fact table.
Then create a slicer Drag and drop the column 2 from the Slicer table in the field pane.
Next add the check measure in the visual level filter( table visual) in the filter pane.
And set show itenm when the value is 1. Apply filter.

In the slicer select A, you can see the A releated single value and multiple value in the table visualization.

Read How to set default value in Power BI Slicer

Power bi filter multiple conditions

Here we will see an example of power bi filter multiple conditions. we will use the below sample table to filter multiple conditions.

In the above table, the number of groups has values in two conditions. So in the above table, the only group that fits is group A, so the count/result is 1.

For example, Group A has the values in C1 and C2. Group B has the values in C2 and in Group C doesn’t contain values in C1 and C2.

So for this, we will create a measure that will filter the condition without blank values.

For this click on the new measure in the ribbon in power bi desktop.
Then write the measure :

Groups without blanks = 
COUNTROWS (
    FILTER (
        VALUES ( Test1[Group] ),
        CALCULATE ( 
            COUNTROWS ( Test1 ), 
            ISBLANK(Test1[Value])
        ) = 0
    )
)

Next we will create the table visualization, and in the values field drag and drop the condition, group, and group without blanks.
In the below screenshot you can see the power bi filter multiple condition.

Power bi filter between two tables

Here we will see an example of power bi filter between two tables in power bi desktop. We will use the below sample tables.

Product Table

Sales Table

Here we will filter the product which is a motorbike and sales amount is greater than 800.

For this we will create a new table which will store the result of the filter value.
Click on the modelling tab -> New table.
Then write the Dax formula to filter the requirements:

New Table = 
FILTER (
    'Product',
    'Product'[Product name] = "Motor bikes"
        && CALCULATE ( AVERAGE ( 'Sales'[Sales]) > 100 )
)

Now select the table visual from the visualization pane, drag and drop the id and product name from the new calculated table.
In the below screenshot you can see the power bi filter between two tables.

Power bi filter between two columns

Here we will see how power bi filters between two columns in power bi desktop.

We will use the below sample table to filter between two columns in the power bi desktop.

We will create two measures which will calculate the total budget and total cost respectively.

Then we will create a calculated column, which will filter the ABD row, because the planned budget is 0 and the actual cost is 0.

So, let’s create the measure, by clicking on the new measure from the ribbon.
Then write the Dax formula for total budget:

Total Budget = SUM(Project[Budget])

Similarly write the Dax formula for Actual cost

Total AC = SUM(Project[Actual cost])

Next we will create a calculated column, which will show if the column returns”True”, when the actual cost and budget is 0, or else it will return false.
Click on Modelling tab-> New column. Then write the below Dax formula:

Test = IF([Total AC]=0 && [Total Budget]=0,"True","False")

Now select the table visual from the visualization pane, drag and drop the Name, Budget and actual cost columns from the field pane.
Then create a slicer and add the Test calculate d column. Select True to filter the table visual for non zero.

You may like the following Power Bi tutorials:

In this power bi tutorial we learned power bi filter and also we discussed the below points:

What is filter in power bi
Types of filter in power bi
Types of filtering method in power bi
How to create filters in power bi dashboard
Power bi button to apply filter
Drill through filters in power bi
How to add date filters in power bi dashboard
Add date range filter in Power bi
Create a filter measure in power bi/done
Custom filter in power bi
Power bi filter between two dates
Power bi filter multiple values
Power bi filter multiple conditions
Power bi filter between two tables
Power bi filter between two columns

Bhawana Rathore

Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com