# Power Bi Filter Functions with Examples

In this Power Bi tutorial, we will learn Power Bi Filter Functions with different examples. And also we will discuss the below points:

• What is Power BI FILTER Function
• List Of Power Bi FILTER Function
• Power BI FILTER Function Example

## What are Power BI FILTER Functions

The Power Bi Filter and Value Functions are some of the most powerful and complex and differ greatly from excel functions.

Whereas the lookup function works by using tables and relationships, like databases. The Filtering functions in Power Bi allow manipulation of data context to create the dynamic calculation.

## List Of Power Bi FILTER Function

Here we will see the list of power bi filter functions with description and syntax.

## Power BI FILTER Function Example

Here we will see the different types of filter functions with examples in Power Bi.

So here we will use the US Superstore Data. And then in Power Bi, I build a Dimensional data model using Power Query.

### Example 1: Using Power Bi All Function

Here we will see how to use the ALL functions in Power bi.

The Power Bi ALL function is a very useful function that all ignores the filters because, in Power bi, the measures are always affected by the filters coming through visuals.

Now we will create a Power BI measure in which we will calculate the total sales, and we will use the Fact sales table inside the ALL expression, then it will ignore all the filters coming from the other tables.

``sales All = CALCULATE(SUM('Fact-Sales'[Sales]),ALL('Fact-Sales'))``

Check out, Power Bi Table Manipulation Functions

### Example 2: Using Power Bi ALLEXCEPT Function

Here we will see how to use ALLEXCEPT Function in Power Bi.

The Power Bi ALLEXCEPT function returns all the rows in a table except for those rows that are affected by the specified column filter.

Now we will create a measure that will sum the sales amount and uses the ALLEXCEPT function to remove any context filter on the dim ship date table except the filter is applied in the date column.

``All except = CALCULATE(SUM('Fact-Sales'[Sales]),ALLEXCEPT('Dim Ship Date','Dim Ship Date'[Date]))``

### Example 3: Using Power Bi ALLNOBLANKROWS Function

Here we will see how to use ALLNONBLANKROWS Function in Power Bi.

The Power Bi ALLNOBLANKROWS Function returns all rows but the blank rows or all the values in a column, ignoring any filter that might have been applied.

So here we have an employee table, having three columns emp name, designation, and Remaining leave columns.

Now we will create a measure that will count the no blank rows that a parent table in a relationship or all the distinct values of a column but the blank row using the ALLNOBLANKROWS function.

``Measure = COUNTROWS(ALLNOBLANKROW(Table1[Leave pending]))``

### Example 4: Using Power Bi ALLSELECTED Function

Here we will see how to use the ALLSELECTED Function in Power Bi.

The ALLSELECTED Function, returns all the rows in a table or all the values in a column by ignoring all the filter that might have been applied inside the query, but keeping filter that comes from outside.

So here we will create a table, in which we will summarize the Product sub category, sales amount, and total sales by using ALLSELCTED Function.

``````Allselected =
CALCULATETABLE (
ALL ( 'Dim-Product'[Sub-Category] ),
"Sales Amount",[total sales],
"Sales Sel",
CALCULATE (
[total sales],
ALLSELECTED ( 'Dim-Product'[Sub-Category] )
)
),
'Dim-Product'[Sub-Category] IN { "Binder","Bookcases","Tables","Appliances"}
)``````

### Example 5: Using Power Bi CALCULATE Function

Here we will see how to use CALCULATE Function in Power Bi.

The Power Bi Calculate Function evaluates an expression in a context modified by filter.

Here we will create a measure in which we will calculate the total sales for the central region.

``Calculate = CALCULATE(SUM('Fact-Sales'[Sales]), 'Fact-Sales'[Region]="Central")``

### Example 6: Using Power Bi CALCULATETABLE Function

Here we will see how to use CALCULATETABLE function Power Bi.

The Power Bi CALCULATETABLE function evaluates a table expression in a context modifies by filter.

Here we will create a calculated table, in which we will show the sub category of product that are coming under office categry.

``````CALCULATETABLE =

CALCULATETABLE (
VALUES ( 'Dim-Product'[Sub-Category] ),
'Dim-Product'[Category]="Office Supplies"
)``````

### Example 7: Using Power Bi EARLIER Function

Here we will see how to use EARLIER Function in Power Bi.

The Power Bi EARLIER Function returns the current value of a column.

Now we will create a column that will count the instance of each date in the order date column. That means 4 January 2014 is present 3 times so the EARLIER Function returns 3

``````COUNTIF Dates Column =
COUNTROWS(
FILTER(
'Fact-Sales',
EARLIER('Fact-Sales'[Order Date]) = 'Fact-Sales'[Order Date]
)
)
``````

### Example 8: Using Power Bi Filter Function

Here we will see how to use FILTER Function in Power Bi.

The power Bi Filter Function is used to filter the rows in the table.

So here we will create a measure that will calculate the to total profit of east region using filter function.

``filter = SUMX(FILTER('Fact-Sales','Fact-Sales'[Region]="East"),'Fact-Sales'[Profit])``

### Example 9: Using Power Bi KEEPFILTER Function

Here we will see how to use KEEPFILTER function in power bi.

The Power Bi KEEPFILTER Function modifies how the filter is applied while evaluating CALCULATE or CALCULATETABLE functions.

So here we will create a measure, to calculate the total sales for the central region using calculate function and keepfilter function.

``Keep filter = CALCULATE([total sales],KEEPFILTERS('Fact-Sales'[Region]="Central"))``

### Example 10: Using Power Bi LOOKUPVALUE Function

Here we will see how to use LOOKUPVALUE Function In Power Bi.

The Power Bi LOOKUPVALUE Function returns the value for the row that meets all criteria specified by one or more search conditions.

So here we will create a measure that will retrieve the customer name according to the row id value using LOOKUPVALUE Function.

``Measure 3 = LOOKUPVALUE('Fact-Sales'[Customer Name],'Fact-Sales'[Row ID],10, "Not Found")``

### Example 11: Using Power Bi SELECTEDVALUE Function

Here we will see how to use SELECTEDVALUE Function in Power Bi.

The Power Bi SELECTEDVALUE Function returns the value when the context for the columnName has been filtered down to one distinct value only.

So, here we will create a measure, if the category of product has more than one region, then it will return more than one or else return the region name.

``Selected value = SELECTEDVALUE('Fact-Sales'[Region],"more than one")``