- Marek Vavrovic

# Filter functions, part one

Updated: Sep 24, 2021

__ALLNOBLANKROW__

__ALLCROSSFILTERED__

__ALLSELECTED__

__ALLEXCEPT__

__REMOVEFILTERS__

**ALL**

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.

**ALL(table)** Removes all filters from the specified table. In effect, ALL(Table) returns all of the values in the table, removing any filters from the context that otherwise might have been applied. This function is useful when you are working with many levels of grouping, and want to create a calculation that creates a ratio of an aggregated value to the total value.

**ALL(<column>,<column>....)** Removes all filters from the specified columns in the table; all other filters on other columns in the table still apply. All column arguments must come from the same table.

syntax

**ALL( [<table> | <column>[, <column>[, <column>[,…]]]] )**

*All column arguments must be from the same table.*

*data*

**Example 1: ALL function**

**Basic measures:**

Whole Table copy = ALL(FactSales) * this will create a snapshot of the entire table*

One column = ALL(FactSales[MarketingAssistant]) *returns distinct list of values*

Two Columns = ALL(FactSales[MarketingAssistant],FactSales[UnitSold]) return the **individual
values**

**Example 2: ALL with SUM, SUMX function**

_SalesAmount = SUMX(FactSales, FactSales[UnitPrice]*FactSales[UnitSold])

The measure in the table on the left is incorrect because the ALL function will only remove filters for *Category column* and in the table I have *Item column* as well. This field needs to be listed in the ALL function, as you can see on the right side.

_SalesAmountALL = CALCULATE([_SalesAmount],ALL(FactSales))

UnitSold ALL = SUMX(ALL(FactSales),FactSales[UnitSold])

**REMOVEFILTERS**

REMOVEFILTERS is like ALL, but it can only be used as a filter argument in CALCULATE.

syntax:

REMOVEFILTERS([<table> | <column>[, <column>[, <column>[,…]]]])

Example

**_UnitSold RemoveFilter = CALCULATE(SUM(FactSales[UnitSold]),
REMOVEFILTERS(FactSales[Item]))**

This measure is displaying the grand total for all the items sold and the grand total for individual salespersons. For example Andrews sold 19 Binders, but 71 Items in total.

Some tables are easier to read....

**UnitSold =SUM(UnitSold)**

**UnitSold ALL = CALCULATE(SUM(FactSales[UnitSold]),REMOVEFILTERS(FactSales))**

**ALLEXCEPT**

Removes all context filters in the table except filters that have been applied to the specified columns.

syntax:

**ALLEXCEPT(<table>,<column>[,<column>[,…]])**

Removes all context filters in the table except filters that are applied to the specified columns. This is a convenient shortcut for situations in which you want to remove the filters on many, but not all, columns in a table.

**Example 1**

The following measure formula sums UnitSold and uses the ALLEXCEPT function to remove any context filters on the FactSales table except if the filter has been applied to the MarketingAssistant column.

ALLEXCEPT(FactSales, FactSales[MarketingAssistant])) argument returns the same as REMOVEFILTERS(FactSales[Items]) argument.

**Example 2**

In this example I created a measure which calculates the sum by month. The table contains two years of data, 2-3 records per month. I got the correct result back, after I have dragged the Year_month column inside my table.

SaleByYear_Month = CALCULATE(SUM(FactSales[Sales]), ALLEXCEPT(FactSales,FactSales[Year_month]))

**ALLSELECTED**

Removes context filters from columns and rows in the current query, while retaining all other context filters. The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters. This function can be used to obtain visual totals in queries.

ALLSELECTED is typically used as a CALCULATE modifier.

CALCULATE ( ..., ALLSELECTED () )

CALCULATE ( ..., ALLSELECTED ( table ) )

CALCULATE ( ..., ALLSELECTED ( table[column] ) )

*data:*

**Example 1**

If **nothing is selected** this measure returns the **grad total** of the items sold.

If a **Person is selected** or any other category, this measure returns a** total** for the selected category.

_ItemsSold ALLSELECTED = CALCULATE(SUM(StoreA[ItemsSold]),ALLSELECTED())

**Example 2**

If **nothing is selected** this measure returns the ** total **for the individual items in the *Item column.* 17 Desks and 26 Pens sold in total.

* *If a **Person is selected** , it removes the filters for the Person column, this function returns **SUM(StoreA[ItemsSold]).**

_ItemSold ALLSELECTED <PERSON> = CALCULATE(SUM(StoreA[ItemsSold]),ALLSELECTED(StoreA[Person]))

**Example 3**

If **nothing is selected**, ALLSELECTED(StoreA[Item])) returns **the total** for each person in the Person column. Gill sold 15, Parent 13 and Smith 15 in total. ALL() and ALLSELECTED() returns the grand total.

If **Item is seleced, **ALLSELECTED(StoreA[Item])) returns ** SUM(StoreA[ItemsSold]). **ALL() keeps returning the grand total (43). ALLSELECTED() returns the total for the selected Ithem <DESK> which is 17.

_ItemsSold ALLSELECTED <ITEM> = CALCULATE(SUM(StoreA[ItemsSold]),ALLSELECTED(StoreA[Item]))

**ALLCROSSFILTERED**

Clear all filters which are applied to a table. ALLCROSSFILTERED can only be used to clear filters but not to return a table.

*syntax: *

**ALLCROSSFILTERED(<table>)**

**Example 1**

**in the measure**

Returns the same result as ALL function, removes all the filters on the table.

ALLCROSSFILTER = CALCULATE(SUM('ALL Table copy'[UnitSold]),ALLCROSSFILTERED('ALL Table copy'))

ALL = CALCULATE(SUM('ALL Table copy'[UnitSold]),ALL('ALL Table copy'))

**Example 2**

**in a new Table**

*All the functions are returning the same result(ALLCROSSFILTERED,ALL, ALLSELECTED,ALLEXCEPT)*

**Working with null values**

**ALLNOBLANKROW**

From the parent table of a relationship,** returns** all rows but the blank row, or **all distinct values **of a column but the blank row, and disregards any context filters that might exist.

syntax:

**ALLNOBLANKROW( {<table> | <column>[, <column>[, <column>[,…]]]} )**

*DATA:*

COUNTX> counts a number of rows that are not blank

_COUNTX = COUNTX(DimCustomer, DimCustomer[Birthdate])

COUNTBLANK>counts a number of blanks in the column

_COUNTBLANK = COUNTBLANK(DimCustomer[Birthdate])

VALUES> returns the distinct values from the specified column

ALLNOBLAKROW> returns the distinct values from the specified column