EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Data Science Data Science Tutorials Power BI learn Power BI Filter

Power BI Filter

Priya Pedamkar
Article byPriya Pedamkar

Updated March 22, 2023

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

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
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • 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

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

EDUCBA

Download Power BI Filter Excel Template

EDUCBA

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

Let’s Get Started

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

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

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

Forgot Password?

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

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

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW