Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Getting Started with the DAX queries for SQL Server Analysis Services


By:   |   Updated: 2015-10-28   |   Comments (1)   |   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

Getting started

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

query results


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

query results

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:

query results


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:

query results

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:

query results

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:

query results

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:

query results

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:

query results

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:

query results
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
For more information about DAX, check this link:

Last Updated: 2015-10-28


get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, March 07, 2018 - 11:07:00 AM - NKP Back To Top

 

 Wow!! this is what I was searching exactly. I wanted to start quickly with SSMS using DAX expressions and this tutorial exactly did that. 

My next step is to get familiarity with more DAX functions to write complex calculations


Learn more about SQL Server tools