EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials PowerBI Tutorial Power BI Filter
Secondary Sidebar
PowerBI Tutorial
  • Advanced
    • Power BI Tutorial
    • KPI in Power BI
    • Power BI Calendar
    • Power BI Dashboard Samples
    • Power BI DAX
    • Power BI Filter
    • Power BI Icon
    • Slicers in Power BI
    • Power BI IF Statement
    • Power BI Template
    • Power BI Maps
    • Measures in Power BI
    • Power BI Report
    • Power BI SUMX
    • Power BI Charts
    • Power BI Pie Chart
    • Power BI Bullet Chart
    • Power BI Gantt Chart
    • Power BI Bubble Chart
    • Power BI Waterfall Chart
    • Power BI Drillthrough
    • Power BI Themes
    • Power BI Tooltips
    • Power BI Heat Map
    • Creating Reports in Power BI
    • Power BI Connections
  • Basic
    • What is Microsoft Power BI
    • Is Power BI Free?
    • Power BI Visuals
    • Power BI Parameters
    • Power BI Alternatives
  • Interview Questions
    • Power Bi Interview Questions

Related Courses

Power BI Certification Course

Business Intelligence Certification Course

Data Visualization Certification Course

Power BI Filter

By Priya PedamkarPriya Pedamkar

Power BI Filter

Introduction to Power BI Filter

A filter is a word we use in MS Excel often to see only a specific set of data. In MS-Excel we are all familiar with the drop-down list to choose only items that are required.

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (85,992 ratings)

However, when it comes to Power BI we do have a filter drop-down list but when summarizing the data we need to use DAX function i.e. Filter. For example when we have all the city sales if you want to show only one city sales total then we can use FILTER DAX function to get a total of one particular city.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

What Does Filter Function do in Power BI?

FILTER is simply the DAX function used to summarize the data with specifies criteria’s. As we have told above when we have all the cities sales if you want to show only one city sales total then we can use FILTER DAX function to get the total of one particular city.

FILTER is mainly used with CALCULATE function, in general, to apply any kind of filters to arrive criteria based totals. Below is the syntax of the FILTER function in Power BI.

Syntax of Power BI Filter

  • Table: First we need to mention the table name that we are filtering.
  • Filter Expression: In the filtering table what is the thing we need to filter. In case of filtering individual city sales we need to select a city column and by putting equal sign we need to enter the city name in double-quotes.

How to Use Filter DAX Function in Power BI?

Let’s see some of the examples to understand the functionality of the Filter DAX function in Power BI.

Power BI Filter – Example #1

For demonstrating the Filter function consider the below data table that we are going to use. So you can download the excel workbook from the below link which is used for this example.

You can download this Power BI Filter Excel Template here – Power BI Filter Excel Template

Power BI FILTER Example 1-1

We have two tables “Sales_Table” and “Incentive_Table”.

  • Upload these two tables to Power BI Desktop file by downloading the excel workbook.

Power BI FILTER Example 1-2

  • Now insert Table visual and create a summary like the below one.

Power BI FILTER Example 1-3

This shows each respective total, now imagine a situation where we need to have a sales summary for each city for the year “2015”. This is where we can include the “FILTER” function to filter only for the year 2015.

  • Right-click on the Sales_Table and choose the New Measure option.

Power BI FILTER Example 1-4

  • Give name as the 2015 Year Sales.

Power BI FILTER Example 1-5

  • Since we need to sum sales values for the year 2015, open the CALCULATE function first.

CALCULATE function Example 1-6

The first argument of the CALCULATE function is Expression i.e. what is the calculation we need to do, so we need to sum sales value column.

  • So open SUM function and choose the “Sales” column from “Sales_Table”.

SUM function Example 1-7

As of now, this will sum the “Sales” column now next argument is Filter1 i.e. while doing the sum of sales column what is the filter condition we need to apply.

  • So open FILTER function to apply filter condition.

Power BI FILTER Example 1-8

  • Mention the table name for which we are applying the filter. The table we are applying a filter for is Sales_Table so choose the same table name.

Power BI FILTER Example 1-9

  • Filter Expression that we are applying is for the column Order Date, so choose this column.

Power BI FILTER Example 1-10

  • Since this is a complete date column we need to choose the Year item from this column. So put a dot and choose the Year item.

Choose Year Example 1-11

  • Now apply the filter criteria as 2015.

Power BI FILTER Example 1-12

  • Close two brackets and hit enter key to get the sales value for the year 2015 only.

Power BI FILTER Example 1-13

  • Drag and drop this new measure i.e. 2015 Year Sales to the table visual to get the year 2015 total for each city.

Power BI FILTER Example 1-14

  • This will give visualization as shown below.

Power BI FILTER Example 1-15

  • Now imagine a situation where you need to apply multiple filters, for an example we have already created filter for the year 2015, now let’s say we need to have these sales total only for the state “Texas” in this case we can continue the old formula and after applying FILTER put comma to access next argument of CALCULATE function.

Power BI FILTER Example 1-16

  • The next option of the CALCULATE function is Filter 2 so for this open another FILTER function.

Power BI FILTER Example 1-17

  • Again choose the table as Sales_Table.

Sales_Table Example 1-18

  • Filter Expression that we need to apply for the column is “State” so choose the “State” column.

Power BI FILTER Example 1-19

  • For this column we need only “Texas” state sales total for the year 2015, so put an equal sign and enter the criteria as Texas.

Criteria Texas Example 1-20

  • Now close two brackets and hit enter key to get the total.

Power BI FILTER Example 1-21

As you can see above since we have edited the existing formula we have sales value only for the city “Texas” and for the year “2015”.

Power BI Filter – Example #2

Now, look at one more example of using FILTER. Assume we need to calculate the incentive amount based on the “State” column, for each state we have different incentive percentage, so we need to fetch the incentive percentage from another table.

We will use the RELATED function to fetch the incentive details. Assume we need to create a measure that calculates the incentive except for the state “Kentucky”, Right-click on Sales_Table and choose New Measure and give the name as “Incentive Except Kentucky”.

Incentive Except Kentucky Example 2-1

  • Open the CALCULATE function.

CALCULATE function Example 2-2

  • The calculation that we need to perform is we need to multiply the sales value with incentive percentage which is there in the other table, so open SUMX function.

SUMX function Example 2-3

  • Mention the table name.

Power BI FILTER Example 2-4

  • An expression is nothing but the kind of calculation that we need to do, so first, choose Sale Value column.

Power BI FILTER Example 2-5

  • Sales Value column needs to be multiplied with the incentive percentage column from another table, so open RELATED function.

RELATED function Example 2-6

  • The Colum Name that we need to choose from Incentive_Table is “Incentive %”, so choose the same.

Power BI FILTER Example 2-7

  • Close two brackets and this will be the end of the calculation, now open FILTER function.

Power BI FILTER Example 2-8

  • Mention the table name as Sales_Table.

Power BI FILTER Example 2-9

  • Filter Expression will be for the state except “Kentucky”, so enter the filter criteria as shown below.

Power BI FILTER Example 2-10

  • Ok, that all close the brackets and hit enter key to get the result.
  • Drag and drop this new measure i.e. Incentive Except Kentucky to the table visual to get the incentive values.

Power BI FILTER Example 2-11

  • This will give visualization as shown below.

Power BI FILTER Example 2-12

Wow!!! As you can see above we have incentive values for all the states except for the state “Kentucky”.

NOTE: Power BI Filter file can also be downloaded from the link below and the final output can be viewed.
You can download this Power BI Filter Template here – Power BI Filter Template

Things to Remember

  • In Power BI, the FILTER function is commonly used with the CALCULATE function.
  • A RELATED function is used to fetch the data from another table if there is a relationship between two tables.

Recommended Articles

This is a guide to Power BI Filter. Here we discuss Power BI Filter Function which is used to summarize the data with specifies criteria along with a practical example. You may also look at the following articles to learn more –

  1. Use of Power BI IF Statement
  2. Power BI Calendar (Examples)
  3. Introduction to KPI in Power BI
  4. How to Create Power BI Reports?
  5. Top Features of Power BI for Data Visualization
  6. Learn the Power BI Bubble Chart
  7. Types of Connections available in Power BI
Popular Course in this category
Microsoft Power BI Training (6 Courses, 4 Projects)
  6 Online Courses |  4 Hands-on Projects |  45+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Business Intelligence Training (12 Courses, 6+ Projects)4.9
Data Visualization Training (15 Courses, 5+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Power BI Filter Excel Template

EDUCBA

Download Power BI Filter Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more