SQL Server DAX Filtering Data
By: Siddharth Mehta
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.
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.
- Try out different DAX Filter functions with the evaluate clause to learn how to develop complex filter criteria.