SQL Server DAX Filtering Data

By: Siddharth Mehta

Overview

It is rarely the case that one needs all of the data from the model or a table without using any criteria. In order to specify a criteria with the evaluate clause to extract the intended data, DAX provides the filter function. This function works in a way similar to the WHERE clause in T-SQL. In this chapter we will learn how to filter the selected data using the DAX Filter function.

Explanation

Open SSMS, connect to the tabular SSAS instance where the AdventureWorks tabular model is deployed and open a new MDX query window. All the perspectives that we saw in the last chapter, will be listed in the Cube pane. Select the Internet Operation perspective in the Cube pane and type the below expression.

evaluate ( filter ('Sales Territory', [Sales Territory Country] = "United States") )

Execute the above expression and you should be able to find the results as shown below.

Let's say, that we need to filter the dataset coming out of the first filter function. We can add any number of filter functions by nesting one filter function inside another as shown below.

evaluate ( 
   filter (
      filter ('Sales Territory', [Sales Territory Country] = "United States") , [Sales Territory Region] = "Central")
          )
         )

The above query can also be re-written as below.

evaluate ( 
   filter ('Sales Territory', [Sales Territory Country] = "United States" && [Sales Territory Region] = "Central")
         )

In this way, we can use different operators and functions to create a complex filter criteria. You can read more about the filter functions here.

Additional Information

• Try out different DAX Filter functions with the evaluate clause to learn how to develop complex filter criteria.