DAX Queries, Part 2

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.

14 responses

    • 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.

  1. Pingback: SQL Server “Denali” CTP 3 is Out–Best Posts & my top picks (Business Intelligence Features) « Rui Quintino Blog

  2. Pingback: Controlling the Position of Subtotals in DAX with GenerateAll() « Chris Webb's BI Blog

  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.

  4. Pingback: DAX Queries, Part 1 « Chris Webb's BI Blog

  5. Pingback: KPI’s in Power View and DAX Query for KPI’s - Calvin Fern's Blog

  6. Pingback: Ms SQL Girl | DAX In A Jiffy: DAX Queries in SSMS

  7. Chris,
    I am stuck at a point where my shared(main) dataset is being used by multiple reports.The requirement is to have a multi value parameter as optional for one report and mandatory for the other report.I googled the web a lot example:to allow null within the drop down etc but didnt find the solution.My main dataset query contains the below in filter like:
    PATHCONTAINS (
    SUBSTITUTE (
    SUBSTITUTE ( SUBSTITUTE ( @Salesperson, “{ “, “” ), ” }”, “” ),
    “&”,
    “|”
    ),
    ‘SalesPeople'[SALESPERSON]
    )
    This works for the report to allow users to select muliple values for 1 report,but the other report complains of multiple issues because I do not know how to condition the optional part in the main query.

      • Basically,I have a salesperson DAX that populates the @Salesperson.I have a shared dataset :
        EVALUATE
        (
        ADDCOLUMNS (
        FILTER (
        SUMMARIZE (
        ‘Revenue’,
        ‘Revenue'[Account],
        ‘Revenue'[Status],
        ‘SalesPeople'[SALESPERSON]
        ),
        PATHCONTAINS (
        SUBSTITUTE (
        SUBSTITUTE ( SUBSTITUTE ( @Salesperson, “{ “, “” ), ” }”, “” ),
        “&”,
        “|”
        ),
        ‘SalesPeople'[SALESPERSON]
        ),
        “Quantity sum”, ‘RevenueAndCost'[Sum of Quantity]
        )
        )

        I want to make @Salesperson as optional in one report and mandatory for the other report.The parameter populates all the salesmen with “select all” value and works fine for the report where users select some values.But this fails for the other report where I am passing the parameter as internal with “All” selected as default.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: