Getting Started with the DAX queries for SQL Server Analysis Services

By: Daniel Calbimonte   |   Comments (2)   |   Related: > Analysis Services Tutorials

Problem

I would like to learn the DAX query language for SQL Server Analysis Services Tabular Data, is there a simple tutorial to get started?

Solution

In this tip, we will work with DAX for newbies who want to get started.

DAX means Data Analysis Expressions and it is a simpler query language than MDX. MDX or Multidimensional Data Expressions is the language used for the Analysis Services Multidimensional Databases. DAX started as a function language and an extension of Excel in Power Pivot. Now it is a query language used in Tabular Databases. Tabular Databases started in SQL Server 2008 R2 and now the functionality and popularity is increasing.

Requirements

• SQL Server 2012 or later with a Tabular Instance installed.
• The AdventureWorks Tabular solution installed.

Getting started

In order to start, open SSMS and Connect to a Tabular Server.

In SSMS, right click on the database, select New Query and MDX. Currently MDX is used to execute MDX and DAX queries.

DAX Evaluate

The most basic statement is the Evaluate, which is like a SELECT in T-SQL.

evaluate 'Currency'

The evaluate statement contains the table expression to show the results. In this example, Currency is the dimension contained in the Tabular Database. The result displayed by the DAX query is the following:

You can also use parenthesis with the Evaluate statement and obtain the same results of the statement as step 3.

evaluate
  (
   'Currency'
  )

In order to show the values of a single column, you can use Evaluate values and specify the table and the column. In this sample, the table (which is a dimension) is currency and the column is CurrencyKey.

 evaluate(values('Currency'[CurrencyKey]))
	 

The result displayed by the query is the following:

DAX Date Functions

To handle dates, you can use Now(), to show the current date and time. You also use the function Year(), to get the year of a specified date and you can also use Month(), Day(), Hour(), Minute() and Second() with a specified date.

evaluate(
	ROW 
	(
		"CurrentDate",Now(),
		"Year",Year(Now()),
		"Month",Month(Now()),
		"Day",Day(Now()),
		"Hour",Hour(Now()),
		"Minute",Minute(Now()),
		"Second",Second(Now())
	)
)  

The results displayed by the query are the following:

DAX Aggregate Functions

It is also possible to show the maximum, average, minimum, sum or count of rows using DAX. The following sample shows how to get the minimum, average, maximum, count of rows and sum of the CurrencyKey column.

 evaluate(
         row(
					
              "Minimum Value", min('Currency'[CurrencyKey]) ,
		         "Average Value", average('Currency'[CurrencyKey]) ,
              "Maximum Value", max('Currency'[CurrencyKey]),
		         "Count",count('Currency'[CurrencyKey]),
		         "Sum",sum('Currency'[CurrencyKey])
           )
)

Here you have the results of the query:

DAX Filtering Queries

You can also add filters to your queries. The following sample shows the data of the Currency dimension where the CurrencyKey is equal to 5.

 evaluate( filter('Currency', [CurrencyKey] = 5))
 

The result displayed by the query is the following:

DAX Ordering Data

You also specify the order in DAX to order the rows by one or more columns. Here is a sample of the Customer dimension ordered by the First Name and the Last Name.

evaluate ('Customer')
ORDER BY [First Name],
[Last Name]
 

The result displayed by the query is the following:

You can see above, the Customer First Name is Aaron, we can also start the query at another row as shown below.

DAX Start At Specific Record

The START AT helps you to show the data at a START column value. In this sample, the query will show the data starting at the First Name equal to Courtney.

evaluate ('Customer')
ORDER BY [First Name],
[Last Name]
START AT "Courtney"
 

The first name as you can see starts with Courtney with the new query:

Conclusions

In this chapter, we showed how to create simple queries using DAX. We used simple selects, aggregate functions and date functions. We also learned how to use filters and how to order the data. As you can see, DAX is not so very complicated and it is much simpler than MDX.

In future tips we will show more queries using DAX.

Next Steps

• Data Analysis Expressions (DAX) Reference

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips