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.
Select * from [Internet Sales]
Evaluate( 'Internet Sales' )
View a Single Column
There is a slight twist here in that the DAX values() function only returns distinct values.
Select distinct OrderDateKey from Internet Sales
evaluate( values('Internet Sales'[OrderDateKey]) )
Count the Rows
Count the number of rows in a table.
Select count(*) from [Internet Sales]
evaluate( ROW ( "Count" ,COUNTROWS('Internet Sales') ) )
Get a distinct count of a particular column.
Select count(distinct [ProductKey]) as ‘No Distinct Products’ From [Internet Sales]
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.
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.
Select * from Internet Sales where ProductKey = 373
evaluate( filter('Internet Sales', [ProductKey] = 373))
To add multiple filters we just need to nest them.
Select * from Internet Sales where ProductKey = 373 and OrderDateKey > 20080000
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
Select * from Internet Sales order by ProductDateKey
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.
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.
Select TOP 10 WITH TIES * from [Internet Sales] order by OrderDateKey
evaluate( TOPN(10, 'Internet Sales', [OrderDateKey]) )
Be careful to use double quotes when specifying the column headings for your aggregated column (“Total Sales”).
Select ProductKey, SUM([Sales Amount]) from [Internet Sales] Group by ProductKey
evaluate( summarize( 'Internet Sales', [ProductKey], "Total Sales", SUM('Internet Sales'[Sales Amount] ) ) )
And for multiple aggregations over columns, with ordering…
Select ProductKey, PromotionKey, SUM([Sales Amount]) , AVG([Gross Profit] ) from [Internet Sales] Group by [ProductKey], [PromotionKey] Order by [ProductKey], [PromotionKey]
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]
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.
SELECT [ProductKey] ,sum([SalesAmount]) AS 'Sum Total Sales' , RANK() OVER ( ORDER BY SUM([SalesAmount]) DESC) AS Rank FROM [FactInternetSales] GROUP BY ProductKey
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.
Select Min(OrderDateKey) MinOrderDate , Max(OrderDateKey) MaxOrderDate From [Internet Sales]
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()
Select getdate() as ‘Timestamp’
evaluate( ROW ( " Timestamp’" ,NOW() ) )
To Convert() a value to a datetime
select convert(datetime, '01/01/1900') as ‘Beginning of time’
evaluate( ROW ( "Beginning of time" ,DATEVALUE( "01/01/1900") ) )
For the Year(), Month(), Day() functions
Select YEAR(0) as 'First Year' , MONTH(0) as 'First Month' , DAY(0) as 'First Day'
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
select dateadd(month,-1,getdate()) as 'One month previous'
evaluate( ROW ( "One month previous" ,eDate(now() ,-1) ) )
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.
Select PATINDEX( 'a', 'ABC' ) AS [Pattern index]
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 ) ) )
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.
Select replace( "the pen is blue", "blue", "red") [Pen colour] , stuff( "the pen is blue",5,3,"sky") [Sky colour]
evaluate( ROW ( "Pen Colour", Substitute("The pen is blue","blue","red"), "Sky Colour", Replace("The pen is blue", 5,3, "Sky") ) )
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.
Select cast("4" as int) as 'Number 4'
Evaluate ( ROW ( "Number 4", VALUE("4") ) )
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.