Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

DAX Queries, Part 1

with 10 comments

So at long last Denali CTP3 has been released and I can blog about all the cool new stuff in it. It feels like years (in fact it has been years) since I’ve had anywhere near this much new functionality to learn and discuss here – it’s better than Christmas! And where better to start than with DAX queries? We’ve had DAX calculations for a while now, of course, but now in BISM Tabular mode we can actually write queries in DAX too. Let’s take a look at how the language works…

For the purposes of this post and the others in this series I built a very simple Tabular mode with two tables from the Adventure Works database, DimDate, DimProduct, DimProductSubCategory, DimProductCategory and FactInternetSales.

image

(I know I’ve not talked about how you would actually go about building a Tabular model yet, but this post from the BISM team is a good starting point and in any case it’s not too different from how you’d build a PowerPivot model)

The simplest DAX query we can write on this model is probably this:

evaluate(
FactInternetSales
)

This returns every row and every column from the FactInternetSales table. Here’s what you see when you run this query in an MDX query window (!) in SQL Server Management Studio:

image

As you can see, the Evaluate statement is at the heart of the new DAX query language: it returns the results of a DAX table expression as the results of a query.

We can filter the rows we receive from a query by using the Filter function:

evaluate(
Filter(
FactInternetSales
, FactInternetSales[OrderDateKey]>20030101)
)

image

Here, I’m getting all the columns from FactInternetSales but only those rows where the OrderDateKey is greater than 20030101.

We can also order our resultset by using an Order By clause, for example:

evaluate(
Filter(
FactInternetSales
, FactInternetSales[OrderDateKey]>20030101)
)
order by
FactInternetSales[OrderDateKey] asc,
FactInternetSales[CustomerKey] asc

image

And there’s also a Start At clause where, for every column that appears in the Order By clause, you can specify to start the result set at the first row where a particular set of values appears; I would imagine that this will be useful for pagination in reports. If you compare the results of the following query with the results of the previous query:

evaluate(
Filter(
FactInternetSales
, FactInternetSales[OrderDateKey]>20030101)
)
order by
FactInternetSales[OrderDateKey] asc,
FactInternetSales[CustomerKey] asc
start at
20030104,
23120

image

…you can see that our resultset now starts at the row where OrderDateKey = 20030104 and CustomerKey=23120.

In part 2, I’ll take a look at the Summarize function…

Written by Chris Webb

July 13, 2011 at 11:59 pm

Posted in DAX

10 Responses

Subscribe to comments with RSS.

  1. [...] things they couldn’t before. So check out various posts such as this from Marco Russo, and this interesting series from Chris [...]

  2. [...] on from my last post, let’s now see how we can do group-by style in DAX [...]

  3. HI Chris ,

    Do you think in near future DAX is going to replace MDX ?.. What would be the future of analysis services ?… Does the multidimensional model is going to stop in cming years ?..

    Rajesh

    July 18, 2011 at 2:42 pm

    • That’s the million-dollar question! I’ve given my opinion several times here; I can only say you should read my previous blog posts on the subject (which, incidentally, record my opinions at different points in time and which may not reflect my opinion today or in the future).

      Chris Webb

      July 18, 2011 at 2:51 pm

  4. Hi Chris

    The “start at” phrase appears to just be another way to filter the data. Have I misunderstood? If not, why would we not just use the Filter function?

    Thanks
    Craig

    Craig Bryden

    March 22, 2012 at 11:28 pm

    • Good question – I’m not sure if there are any differences between Filter and Start At as far as performance etc goes. I’ll try to find out…

      Chris Webb

      March 22, 2012 at 11:31 pm

  5. [...] want to learn about DAX queries take a look at the posts I wrote on this topic last year, starting here), and if you’ve already learned a lot of DAX for PowerPivot you’re probably going to be more [...]

  6. [...] DAX Basics in 30 Minutes •http://www.sqlbi.com/ •Chris Webb’s blog. 6 lessons about DAX start here •Converting MDX to DAX – First [...]

  7. [...] Here I’m filtering the DimDate table so that the only rows displayed are where day number of month is greater than 21, and day name of week is either Monday or Saturday. If you’re interested in learning more about writing DAX queries, check out the series of blog posts I wrote on this topic here. [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,866 other followers

%d bloggers like this: