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
Comments