• Marek Vavrovic

Filter functions, part one

Updated: Sep 24

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




17 views0 comments

Recent Posts

See All

Index

Information functions Filter functions Table manipulation functions Grouping and summarizing Time intelligence functions OPENINGBALANCEYEAR OPENINGBALANCEYEAR(<Expression>,<Dates> [,<Filter>][,<YearEn