Overview
In this chapter we will review the methods of selecting data by evaluating the DAX expressions in SQL Server Management Studio (SSMS). The evaluate clause is the equivalent of the SELECT statement in T-SQL, which can be used to select data from tabular models.
Explanation
Open SSMS, connect to the tabular SSAS instance where the AdventureWorks tabular model is deployed, and open a new MDX query window. In the Cube pane, the dropdown at the top lists all the perspectives in AdventureWorks tabular database. Select the Internet Operation perspective in the Cube pane, and type the below expression.
evaluate ( 'Sales Territory' ) order by 'Sales Territory'[Sales Territory Country] start at "NA"
Execute the above expression and you should be able to find the results as shown below.
Let’s understand this expression in detail:
1) The evaluate clause returns each and every row and column from the table ‘Sales Territory’.
2) The Order By clause sorts the resultset. You can sort the resultset in descending order by using the keyword desc. You can also sort the resultset based on more than one fields by specifying the fields in a comma-separated order.
3) Start At clause can be used for every columns appearing in the order by clause. In the above expression, we have specified to start the resultset where the Sales Territory Field is has a value of NA. Try to execute the query without the Start At clause, and you will be able to see the difference.
4) You can also select specific fields from the table using the evaluate clause. Try the below expression as an example. This expression returns distinct values of the Sales Territory Country.
evaluate ( values ('Sales Territory'[Sales Territory Country]) )
order by 'Sales Territory'[Sales Territory Country]
start at "NA"
In this way, we can use SSMS to evaluate DAX expressions and query the data from a tabular data model.
Additional Information
- Consider reading this DAX Tutorial to learn more about DAX
