In this document I will attempt to talk you through writing your first very simple DAX queries. For the purpose of this document I will query the rather familiar Adventure Works database. Well I say database, we are actually going to be querying a tabular cube. This document will focus solely on the DAX functional syntax.

I will attempt to relate the DAX query examples to the T-SQL equivalent.

## First Up, Casual Browsing

First up we will look at probably the most frequently used casual browsing queries to be executed on any database.

### View a Table

I won’t go in to any detail here as there’s not much to be said, other than DAX does requires that table names containing punctuation need to be quoted using single quotes and T-SQL will allow you to use squared brackets or single quotes.

**T-SQL**

Select * from [Internet Sales]

**DAX**

Evaluate( 'Internet Sales' )

### View a Single Column

There is a slight twist here in that the DAX values() function only returns distinct values.

**T-SQL**

Select distinct OrderDateKey from Internet Sales

**DAX**

evaluate( values('Internet Sales'[OrderDateKey]) )

### Count the Rows

Count the number of rows in a table.

**T-SQL**

Select count(*) from [Internet Sales]

**DAX**

evaluate( ROW ( "Count" ,COUNTROWS('Internet Sales') ) )

### Distinct Count

Get a distinct count of a particular column.

**T-SQL**

Select count(distinct [ProductKey]) as ‘No Distinct Products’ From [Internet Sales]

**DAX**

evaluate( ROW ( "No Distinct Products" ,DISTINCTCOUNT('Internet Sales'[ProductKey]) ) )

## Building Blocks of DAX Queries

I will now provide some more examples of clauses we are familiar with in T-SQL and their DAX equivalents.

### Where Clause

We can filter rows in a DAX query using the filter function which accepts a Boolean evaluation in the same way the T-SQL where clause does.

**T-SQL**

Select * from Internet Sales where ProductKey = 373

**DAX**

evaluate( filter('Internet Sales', [ProductKey] = 373))

To add multiple filters we just need to nest them.

**T-SQL**

Select * from Internet Sales where ProductKey = 373 and OrderDateKey > 20080000

**DAX**

evaluate( filter( filter('Internet Sales', [ProductKey] = 373) , [OrderDateKey] > 20070000) )

Please note that in DAX there are other methods of filtering and un-filtering intermediate result sets, but they are more advance concepts and won’t be covered in this document. Please also note that the nesting of many filters may have sub-optimal effects and this example only illustrates the possibilities. The calculatetable() function is more appropriate for multiple filters.

### Order By Clause

The order by clause should be very familiar

**T-SQL**

Select * from Internet Sales order by ProductDateKey

**DAX**

evaluate( 'Internet Sales' ) order by [ProductKey]

Note that using order by 1,2,…n will be accepted by the query engine but it has no influence on the query.

### TOP Clause

It’s worth noting that the TOPN function in DAX requires an ordering expression (OrderDateKey), rather than allowing the combination of TOPN and Order By. But don’t be fooled into thinking the result will be returned in order because there is no guarantee. This does mean however that a result set can be restricted by a definitive row count and then re-order again by the Order By clause. It’s also worth noting that the DAX equivalent is always with ties. No percentage variation is implemented with the DAX function at this time.

**T-SQL**

Select TOP 10 WITH TIES * from [Internet Sales] order by OrderDateKey

**DAX**

evaluate( TOPN(10, 'Internet Sales', [OrderDateKey]) )

### Aggregation

Be careful to use double quotes when specifying the column headings for your aggregated column (“Total Sales”).

**T-SQL**

Select ProductKey, SUM([Sales Amount]) from [Internet Sales] Group by ProductKey

**DAX**

evaluate( summarize( 'Internet Sales', [ProductKey], "Total Sales", SUM('Internet Sales'[Sales Amount] ) ) )

And for multiple aggregations over columns, with ordering…

**T-SQL**

Select ProductKey, PromotionKey, SUM([Sales Amount]) , AVG([Gross Profit] ) from [Internet Sales] Group by [ProductKey], [PromotionKey] Order by [ProductKey], [PromotionKey]

**DAX**

evaluate( summarize( 'Internet Sales', [ProductKey], [PromotionKey], "Total Sales", SUM('Internet Sales'[Sales Amount]), "Average Gross Profit", SUM('Internet Sales'[Gross Profit]) ) ) order by [ProductKey], [PromotionKey]

### Ranking

The ability to rank results is an important feature is any BI application. DAX has two ranking functions; RANKX() and RANK.EQ(). RANK.EQ is the equivalent to the excel ranking function RANK.EQ and allows you to find the rank of a number in a list of numbers. I won't go into any further details regarding RANK.EQ() because RANKX() is the function that you will frequently use for ranked reports. RANKX() ranks a value against a range of values. In the example below the internet sales amount for a product is ranked against the internet sales amount of all products.

**T-SQL**

SELECT [ProductKey] ,sum([SalesAmount]) AS 'Sum Total Sales' , RANK() OVER ( ORDER BY SUM([SalesAmount]) DESC) AS Rank FROM [FactInternetSales] GROUP BY ProductKey

**DAX**

EVALUATE ( SUMMARIZE ('Internet Sales' ,[ProductKey] ,"Sum Internet Sales", sum('Internet Sales'[Sales Amount]) ,"Rank",RANKX(ALL('Internet Sales'[ProductKey]),SUMX(RELATEDTABLE('Internet Sales'),[Sales Amount])) ) ) order by [Rank]

## Min and Max

The MIN() and MAX() functions is DAX work in much the same way as T-SQL.

**T-SQL**

Select Min(OrderDateKey) MinOrderDate , Max(OrderDateKey) MaxOrderDate From [Internet Sales]

**DAX**

evaluate( row( "MinOrderDate", min('Internet Sales'[OrderDateKey]) , "MaxOrderDate", max('Internet Sales'[OrderDateKey]) ) )

### Dates and Time

DAX has a vast array of datetime functions and the concept of time being a contiguous dimension of any event is implemented natively as part of SSAS. Time dimension details will not be covered in this document, but I will give some examples of basic functionality.

To get the current date with Getdate()

**T-SQL**

Select getdate() as ‘Timestamp’

**DAX**

evaluate( ROW ( " Timestamp’" ,NOW() ) )

To Convert() a value to a datetime

**T-SQL**

select convert(datetime, '01/01/1900') as ‘Beginning of time’

**DAX**

evaluate( ROW ( "Beginning of time" ,DATEVALUE( "01/01/1900") ) )

For the Year(), Month(), Day() functions

**T-SQL**

Select YEAR(0) as 'First Year' , MONTH(0) as 'First Month' , DAY(0) as 'First Day'

**DAX**

The execution of date() here returns 31/12/1899, which appears to be a bug. As year() correctly returns 1900

evaluate( ROW ( "First Year" , Year(Date(1900,1,1)), "First Month", Month(Date(1900,1,1)), "First Day", Day(Date(1900,1,1)) ) )

To perform simple date arithmetic

**T-SQL**

select dateadd(month,-1,getdate()) as 'One month previous'

**DAX**

evaluate( ROW ( "One month previous" ,eDate(now() ,-1) ) )

## Text Processing

This section looks at various string related queries.

### Find & Search

The DAX find() and search() functions are a rather curious implementation. There are subtle differences, with the case-sensitivity characteristic being the most obvious. Search() will ignore case and find() will retain case-sensitivity. Both find() and search() functions can be compared to the patindex() T-SQL function but they extend the functionality to include start indices and default return location parameters.

**T-SQL**

Select PATINDEX( 'a', 'ABC' ) AS [Pattern index]

**DAX**

evaluate( ROW ( "FIND", FIND( "A", "ABC" ), "Search", SEARCH( "a", "ABC" ) ) )

With start-location and default return parameters, the search starts at the second character and returns -1 if the search/find is unsuccessful.

evaluate( ROW ( "FIND", FIND( "A", "ABC",2,-1 ), "Search", SEARCH( "a", "ABC", 2,-1 ) ) )

**Replace-Substitute, Stuff-Replace**

In DAX we can compare the T-SQL replace() function to the DAX Substitute() function and the T-SQL stuff() function to the DAX Replace() function. Which is a little confusing, but here are some examples.

**T-SQL**

Select replace( "the pen is blue", "blue", "red") [Pen colour] , stuff( "the pen is blue",5,3,"sky") [Sky colour]

**DAX**

evaluate( ROW ( "Pen Colour", Substitute("The pen is blue","blue","red"), "Sky Colour", Replace("The pen is blue", 5,3, "Sky") ) )

##### **Value**

The Value() function can be compared with the T-SQL cast or convert functions. It takes a string literal representation of a number and converts it to a number type.

**T-SQL**

Select cast("4" as int) as 'Number 4'

**DAX**

Evaluate ( ROW ( "Number 4", VALUE("4") ) )

## Conclusion

I have only discussed a small subset of the DAX language and there are many more functions and techniques to discover. Hopefully this document gives you a good introduction into the DAX query language. However please bear in mind that Relational and Tabular databases are very different species. So although there are many paradigms between the languages the approach to data retrieval is very different.

In a follow up article I will progress into more complex DAX topics like filter contexts and multi table querying.