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.

Function	Description	Syntax
ALL	This function returns all the rows in a table or all the values in a column, ignoring any filter that might have been applied.	ALL([<table> |<column>[, <column>[, <column>[,..]]]])
ALLCROSSFILTERED	This function clear all filter which is applied to a table.	ALLCROSSFILTERED (<table>)
ALLEXCEPT	This function removes all context filters in the table that have been applied to the specified.	ALLEXCEPT (<table>,<column>[,<column>[,…]])
ALLNONBLANKROW	From the parent table of a relationship, this function returns all rows but the blank rows and disregards any context filter that might exists.	ALLNONBLANKROW ({<table>|<column>[, <column>[,<column>[,…]]]})
ALLSELECTED	This function removes context filters from columns and rows in the current query while retaining all other context filters or explicit filters.	ALLSELECTED ([<tableName> | <columnName>[,<columnName>[,<columnName>[,…]]]])
CALCULATE	This function evaluates an expression in a modified filter context.	CALCULATE (<expression>[, <filter1>[, <filters2>[,…]]])
CALCULATETABLE	This function evaluates the table expression in a modified filter context.	CALCULATETABLE (<expression>[ <filter1>[, <filter2>[,….]]])
EARLIER	This function returns the current value of the specified column in an outer evaluation pass of the mentioned column.	EARLIER (<column>,<number>)
EARLIEST	This function returns the current value of the specified column in an outer evaluation pass of the specified columns.	EARLIEST (<column>
FILTER	This function returns a table that represents a subset of another table or expression.	FILTER (<table>,<filter>)
KEEPFILTER	This function modifies how filters are applied evaluating a CALCULATEn or CALCULATABLE function.	KEEPFILTER (<expression>)
LOOKUPVALUE	This function returns the value of the row that meets all criteria specified by the search condition. This function can apply one or more search condition.	LOOKUPVALUE (<result_columnName>, <search_columnName>, <Search_Value> [, <search2_ColumnName>,<search2_value>]…[, <alternate result>])
REMOVEFILTERS	This function clears filters from the specified column or tables	REMOVEFILTERS ([<table> | <column> [, <column> [, <column>[,…]]]])
SELECTEDVALUE	This function returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult.	SELECTEDVALUE (<columnName>[, <alternateResult>])

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

Read Power Bi Relationship Functions

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 (
    ADDCOLUMNS (
        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"}
)

Read Power Bi Time Intelligence Function

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

Read Power Bi Text Functions with Examples

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

Read Power BI Information Functions

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

Read Power Bi count function

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

Read Power BI average function with Examples

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

You may like the following Power BI articles:

• Power BI MAX and MIN function
• Power BI DAX Logical functions
• Power BI DAX SUM and SUMX function – How to use
• Power BI Slicer Search Box
• How to use Power bi ribbon chart

In this Power Bi tutorial, we learned different types of Power BI Filter functions with examples. And also we discuss the below points:

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