Power BI- DAX Filter Functions

One of the most popular methods of data analysis is filtering the data. No matter if you are a financial expert, a data scientist, a marketer, or a salesperson, data filtering removes the noise and exposes what is relevant. When data is filtered, it typically produces a subset of data that may be used to set up subsequent analyses. Data filtering is unquestionably a feature that Power BI, the most known and well-liked business intelligence tool of our day, provides through the usage of DAX filter capabilities. The DAX filter function, which is a component of the DAX function library, is an iterator function that creates filtered tables for your data models. As output, each row that matches your condition is returned.

DAX Filter Functions

The filter functions in DAX are among the most sophisticated and potent, and they are very different from Excel operations. The lookup functions operate similarly to a database by employing tables and relationships. You can generate dynamic calculations by manipulating the data context with filtering functions.

Let’s practice some of the commonly used DAX Filter Functions on a sample dataset of library supplies companies. The screenshot of the dataset is given below:

DAX Filter All

It Ignores any filters that may have been used and returns all the rows in a table or all the values in a column. This function can be used to remove filters and do calculations across all table rows.

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

• table: The table upon which you intend to clear the filters.
• column: The column upon which you intend to clear filters.

In the below example we are calculating the sum of the Grand Total amount including tax in the current cell, then the SUMX function sums up all the values of the Grand Total of each row.

Example: all = SUMX(‘SLS Order Detials_Master’, ‘SLS Order Detials_Master'[Grand Total amount including Tax (INR)])/SUMX(ALL(‘SLS Order Detials_Master’), ‘SLS Order Detials_Master'[Grand Total amount including Tax (INR)])

DAX Filter AllCrossFiltered

It Removes all table-related filters that have been active.

Syntax: ALLCROSSFILTERED(<table>)

• table: The table upon which you intend to clear the filters.

Example: all cross field = CALCULATE(SUM(‘SLS Order Detials_Master'[Total amount (INR)]), ALLCROSSFILTERED(‘SLS Order Detials_Master’))

DAX Filter AllExcept

Except for context filters that have been applied to the specified columns, remove all context filters from the table.

Syntax: ALLEXCEPT(<table>,<column>[,<column>[,…]])

• table: The table, except for context filters on the columns supplied in subsequent parameters, over which no context filters are applied.
• column: The column that context filters need to be kept in place for.

Example: all except = CALCULATE(SUM(‘SLS Order Detials_Master'[Total amount (INR)]), ALLEXCEPT(‘SLS Order Detials_Master’,’SLS Order Detials_Master'[Order Date (dd-mm-yyyy)]))

DAX Filter AllNoBlankRow

Returns all rows except the blank row, or all distinct values of a column except the blank row, from the parent table of a relationship, ignoring any potential context filters.

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

• table: The table that has had all context filters removed from it.
• column: A column that is devoid of all context filters.

Example:  all no blank row = COUNTROWS(ALLNOBLANKROW(‘FactInternetSales'[OrderDate])) 

DAX Filter AllSelected

Retains all other context filters or explicit filters while removing context filters from columns and rows in the current query. While maintaining explicit filters and contexts other than row and column filters, the ALLSELECTED function obtains the context that represents all rows and columns in the query. Visual totals for queries can be obtained with this function.

Syntax: ALLSELECTED([<tableName> | <columnName>[, <columnName>[, <columnName>[,…]]]] )

• tableName: The name of an already-existing table in DAX format. An expression cannot be used for this argument. This element is not required.
• columnName: The name of an already-existing table in DAX format. An expression cannot be used for this argument. This element is not required.

Example: all selected = calculate(sum(‘SLS Order Detials_Master'[Tax amount (INR)]), ALLSELECTED(‘SLS Order Detials_Master'[Tax amount (INR)]))

DAX Filter Calculate

Interprets an expression in the context of a changed filter.

Syntax: CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])

• expression: The expression that must be assessed.
• filter1, filter2…: Filter modifier functions, table expressions, or Boolean expressions that define filters though optional.

Example: calculate = calculate(sum(‘SLS Order Detials_Master'[Delivery/Forwarding charges]))

DAX Filter CalculateTable

A table expression is evaluated in a changed filter context.

Syntax: CALCULATETABLE(<expression>[, <filter1> [, <filter2> [, …]]])

• expression: The expression that must be assessed.
• filter1, filter2…: Filter modifier functions, table expressions, or Boolean expressions that define filters though optional.

Create a New Table by the following expression and then apply the Calculate Table command.

Table = CALCULATETABLE(‘SLS Order Detials_Master’,’SLS Order Detials_Master'[Client State] = “New Delhi”)

Example: calculate table = COUNTX(CALCULATETABLE(‘SLS Order Detials_Master’,’SLS Order Detials_Master'[Client State] = “New Delhi”),[Client State])

DAX Filter Earlier

In an outer evaluation pass of the given column, returns the current value of the provided column. When you wish to use a certain value as an input and perform calculations depending on that input, EARLIER is helpful for nested calculations. Such calculations are only possible in the context of the current row in Microsoft Excel; but, in DAX, you may store the value of the input and perform the calculation using data from the entire table. EARLIER is frequently used with computed columns.

Syntax: EARLIER(<column>, <number>)

• column: A column or an expression with a column resolution.
• number: A passing score on the external evaluation. One level out from the current evaluation is denoted by 1, two levels out by 2, and so forth. The default value is 1 when omitted. (Optional)

Create a new column in the model with the following command.

Example: Column = CALCULATE(SUM(‘SLS Order Detials_Master'[Unit Price (INR/Unit)]),FILTER(‘SLS Order Detials_Master’,’SLS Order Detials_Master'[Grand Total amount including Tax (INR)]<=EARLIER(‘SLS Order Detials_Master'[Grand Total amount including Tax (INR)])))

DAX Filter Earliest

It gives back the value of the supplied column as of the most recent outer evaluation pass.

Syntax: EARLIEST(<column>)

• column: A mention of a column.

Example: earliest = CALCULATE(SUM(‘SLS Order Detials_Master'[Unit Price (INR/Unit)]),FILTER(‘SLS Order Detials_Master’,’SLS Order Detials_Master'[Tax amount (INR)]<=EARLIEST(‘SLS Order Detials_Master'[Tax amount (INR)])))

DAX Filter FILTER

A table that displays a subset of another table or expression is returned.

Syntax: FILTER(<table>,<filter>)

• table: A table that will be filtered. The table might also be a table-producing expression.
• filter: A Boolean expression that must be tested against each table row. Simply a True or False test.

Example: filter = COUNTROWS(FILTER(‘SLS Order Detials_Master’,’SLS Order Detials_Master'[Client State] = “New Delhi”))

DAX Filter KeepFilters

It alters the way filters are used when a CALCULATE or CALCULATETABLE function is evaluated.

Syntax: KEEPFILTERS(<expression>)

• expression: The expression that must be assessed.

Example: keep filters = CALCULATE(‘SLS Order Detials_Master'[calculate table],KEEPFILTERS(‘SLS Order Detials_Master'[Client State] = “New Delhi”))

DAX Filter RemoveFilters

Remove all filters from the given columns or tables.

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

• table: The table you wish to clear the filters from.
• column: The column on which you want to remove all filtering.

Example: remove filter = calculate(sum(‘SLS Order Detials_Master'[Delivery/Forwarding charges]), REMOVEFILTERS(‘SLS Order Detials_Master'[Product Name]))