Chris Webb's BI Blog

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

DAX Queries, Part 2

with 9 comments

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

The key to aggregating data is the Summarize() function, which is broadly similar to a Group By in SQL. So if the following query returns every row from the FactInternetSales table:

evaluate(
FactInternetSales
)

The following query just returns a list of all the distinct combinations of values from the OrderDateKey and CustomerKey columns:

evaluate(
summarize(
FactInternetSales
, FactInternetSales[OrderDateKey]
, FactInternetSales[CustomerKey]
)
)
order by
FactInternetSales[OrderDateKey]
, FactInternetSales[CustomerKey]

image

Once we’ve specified the name of the table in the first parameter of Summarize, we can then supply a list of columns in the table to group by. This list can contain any number of columns, but we can also do aggregations inside Summarize by supplying a list of column names and DAX numeric expressions after the list of columns. So, for example:

evaluate(
summarize(
FactInternetSales
, FactInternetSales[OrderDateKey]
, “Sum of Sales”
, Sum(FactInternetSales[SalesAmount])
, “Distinct Customers”
, DistinctCount(FactInternetSales[CustomerKey])
)
)
order by
FactInternetSales[OrderDateKey]

Gives us the sum of the SalesAmount column and the number of distinct values in CustomerKey, for each distinct OrderDateKey value, as extra columns in the resultset.

image

We can also reference columns from related tables in our aggregations. So for example this query uses the DimDate table and gives us data aggregated up by year:

evaluate(
summarize(
DimDate
, DimDate[CalendarYear]
, “Sum of Sales”
, Sum(FactInternetSales[SalesAmount])
, “Distinct Customers”
, DistinctCount(FactInternetSales[CustomerKey])
)
)
order by
DimDate[CalendarYear]

image

Finally (at least for today), we can do subtotalling by using the Rollup() function inside our list of group by columns; each column we list inside Rollup() will work like a regular group by column but it will also have a subtotal row added for it in the resultset. So here, for example, is the query above with an extra group by on the days of the week:

evaluate(
summarize(
DimDate
, DimDate[CalendarYear]
, ROLLUP(DimDate[EnglishDayNameOfWeek])
, “Sum of Sales”
, Sum(FactInternetSales[SalesAmount])
, “Distinct Customers”
, DistinctCount(FactInternetSales[CustomerKey])
)
)
order by
DimDate[CalendarYear]

image

I’ve highlighted the subtotals rows here, but we can also identify these rows using the new IsSubTotal() function:

evaluate(
summarize(
DimDate
, DimDate[CalendarYear]
, ROLLUP(DimDate[EnglishDayNameOfWeek])
, “Sum of Sales”
, Sum(FactInternetSales[SalesAmount])
, “Distinct Customers”
, DistinctCount(FactInternetSales[CustomerKey])
, “Is this a SubTotal?”
, if(IsSubtotal(DimDate[EnglishDayNameOfWeek]), “Yes”, “No”)
)
)
order by
DimDate[CalendarYear]

image

 

In part 3, I’ll take a look at how to add derived columns.

Written by Chris Webb

July 15, 2011 at 2:31 pm

Posted in DAX

9 Responses

Subscribe to comments with RSS.

  1. Fantastic post Chris, great examples.

    denglishbi

    July 15, 2011 at 2:40 pm

    • Great post Chris. Dan/Chriss,, I’ve been a very silent follower of Olap Services through SSAS 2k8r2. I was very active conversationally with Mosha 2001/2002 and have been very busy with implementation ever since. Now, I’m felling the same apprehensions you (Chris) did in your earlier posts about denali and the tabular direction. I thought nothing of it until I got reporting service style results in my CTP3 analysis services browser – I’m very upset – where is the pivot functionality – I cant find it :(….. Anyway, following posts avididly in a hop my 12 year career with OLAP isn’t over.

      Mark Morris

      July 16, 2011 at 6:03 am

      • It is unfortunate and many people like that functionality. If you want that you will have to use Excel because the Office Web Components technology is a deprecated feature – http://support.microsoft.com/kb/972129 – and has been for quite some time now.

        denglishbi

        July 17, 2011 at 4:23 pm

  2. [...] It returns the number of Days in the table by calendar year and day number of week – it’s very similar to a basic GROUP BY query in SQL. I blogged about this use of Summarize() and Rollup() last year here. [...]

  3. Hey Chris, I have been going through all of your posts but could not found conversion of KPI functions of MDX in DAX. Functions like KPIValue, KPIGoal, KPIStatus and KPITrend. I am trying to create a SSRS report using a KPI, but for that I need to get its Goal/ Target value and there is no function in DAX which can provide this property of KPI. Please help.

    Digven Singh

    June 19, 2012 at 11:24 am

  4. [...] part 2, I’ll take a look at the Summarize [...]

  5. […] Blog comment from 2012 […]


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,859 other followers

%d bloggers like this: