Overview
In this chapter we will cover the method of selecting data by evaluating DAX expressions in SSMS. The evaluate clause is the equivalent of a 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. 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 ( 'Sales Territory' ) order by 'Sales Territory'[Sales Territory Country] start at "NA"
Execute the above mentioned expression and you should be able to find the results as shown below. And this marks the completion of our very first DAX expression.
Let’s understand this expression in detail.
1) The evaluate clause returns each and every row and column from the table ‘Sales Territory’.
2) 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 field by specifying the fields in a comma-separated order.
3) Start At clause can be used for each column appearing in the order by clause. In the above expression, we have specified to start the resultset where the Sales Territory Field has the value of NA. Try to execute the query without the Start At clause and to see the difference.
4) You can also select specific fields from the table using the evaluate clause. Try the below expression. This expression returns distinct values of Sales Territory Country.
evaluate ( values ('Sales Territory'[Sales Territory Country]) )
order by 'Sales Territory'[Sales Territory Country]
start at "NA"5) To return a scalar value, like count of rows in a table, you can use the below query.
evaluate ( ROW ( "Count" , COUNTROWS('Sales Territory') ) )
In this way, we can use SSMS to evaluate DAX expressions and query the data from a tabular data model.
Additional Information
- Try out different DAX functions with the evaluate clause to fine tune the same.
