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.
Siddharth has more than 14 years of experience in the IT Industry, with more than a decade of experience in Business Intelligence and Analytics, for clients banking, logistics, government, Media Entertainment, products, life sciences and other domains. He has been a lead architect for a portfolio of 40+ apps, containing apps in web, mobile, BI, Analytics, data warehousing, reporting, collaboration, CMS, NoSQL and other technologies. He has several certifications and is a published author for online and print-media publications, as well as the MSDN Library.
In his present role, he remains responsible for architecture design, technology stack selection, infrastructure design, 3rd party products evaluation and procurement, and performance engineering. These applications use technologies like Elasticsearch / Lucene, MongoDB, SharePoint 2013 and 2010, jQuery-based framework like Highcharts and GoJS, SQL Server and the Microsoft Business Intelligence stack (SSIS, SSAS, SSRS, MDX, PowerPivot, PowerView), jQueryMobile, Bootstrap, iOS xCode framework, and many others.
- MSSQLTips Awards: Champion (100+ tips) – 2018 | Author of the Year – 2017 | Author Contender – 2016, 2018-2019
