DAX in SQL Server Filter Function to Filter Data

By: Haroon Ashraf

Overview

So far we have learned to create DAX queries to get tabular data and then aggregating data based on grouping, but what about filtering data? DAX offers very rich filtering which can be achieved by using the Filter function.

The syntax of Filter function is as follows:

Filter(<Table>,<Filter expression>)

Filtering Orders by Total Price

Let's take a simple example of filtering where we are interested to see all the orders where total price is more than 200.

This is expressed in DAX as follows:

EVALUATE(FILTER(Orders,ORDERS[TOTALPRICE]>200.00))

Filtering All Orders by a Specific Customer

In order to see list of all the orders based on a particular customer or in other words if we are only interested in the orders placed by a particular customer then we have to use the Filter function.

Let's make a more complex filter by using Summarize within Filter to see all the orders placed by a particular customer named Sadaf Khan.

There is a quick way to get there using Related (which is out of scope in the current context), but let's take this opportunity to understand how a DAX query is constructed using nesting.

To view all orders placed by customer Sadaf Khan, first create DAX query to view all the orders placed by all the customers using Summarize:

EVALUATE(SUMMARIZE(Orders,orders[OrderID],Customer[Name]))

Now the Summarize used in the above query becomes the first argument of the Filter function since the first argument requires the table or table expression:

EVALUATE(FILTER(SUMMARIZE(Orders,orders[OrderID],Customer[Name]),<table expression>)

Finally we apply the filter expression in the second argument of the Filter function to filter by a particular customer as follows:

EVALUATE(FILTER(SUMMARIZE(Orders,orders[OrderID],Customer[Name]),Customer[Name]="Sadaf Khan")) 

The we can add some more columns to see detailed order information for Sadaf Khan:

EVALUATE(FILTER(SUMMARIZE(Orders,orders[OrderID],Customer[Name],DimDate[MonthName],Orders[Quantity],Orders[TotalPrice]),[Name]="Sadaf Khan"))order by Orders[OrderID]

Filtering All Orders by a Specific Year and Product

Now we are interested to see all the orders placed in the year 2017 by all the customers where the product was Eraser.

Building such a query is again slightly complex especially when we have to use Summarize with Filter despite the fact that there is a quicker way (using Related).

Build a general query to see all the orders placed for all the products in all years:

EVALUATE(SUMMARIZE(Orders,Product[Name],DimDate[Year]))

Next group the orders by product to get total orders in all the years as follows:

EVALUATE(SUMMARIZE(Orders,Product[Name],DimDate[Year],"Total Orders",Count(Orders[OrderID])))

Add filter by passing the Summarize statement as the first argument and Year and Product as the second argument as follows:

EVALUATE(FILTER(SUMMARIZE(Orders,Product[Name],DimDate[Year],"Total Orders",Count(Orders[OrderID])),DimDate[Year]=2017 && Product[Name]="Eraser"))

If we format the DAX query it becomes:

EVALUATE
 (
    FILTER (
        SUMMARIZE (
                   Orders,
                   Product[Name],
                   DimDate[Year],
                   "Total Orders", COUNT ( Orders[OrderID] )
                   ),
        DimDate[Year] = 2017 && Product[Name] = "Eraser"
           )
  )