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.