Kasper de Jonge PowerPivot Blog

Are you new to data modeling in PowerPivot or SQL Server Data Tool (aka BIDS) or never had the chance to start with DAX, now is the time.  We have released online content to help you get started in creating DAX formulas. You can think of this as DAX 101.

The QuickStart: Learn DAX Basics in 30 Minutes is now available as part of the DAX Resource Center Wiki.

Leave a comment if you have some feedback, its a online Wiki so we can change it .

A few weeks ago I got this interesting question from someone. He wants to compare the sales growth of his products in the months since the introduction of the product. Lets say “Product One” first selling date is 1/2/2010 and “Product Two’s” first selling date is 2/1/2010. If we want to compare the sales since introduction we want to compare month 0 between the two products. Month 0 for Product One is 1/2/2010, for Product Two it is 2/1/2010.

I created a table with some data to simulate this:

As you can see different products have different start dates. The first thing we need to do is enrich our table with a new column that will determine the amount of months between the date from the current row and the date of the first sale for this product. Is we have that month we can use this in our report to compare months, first month of sales for each product will be 0, the next month 1 and so forth. This makes comparisons very easy.

Let’s get started with getting the first date for each product. So what do we have to here ? For each sales transaction in the table we need to check all the other sales transaction from the same product to determine the first salesdate. We can do this with a Calculate column using the MINX function:

=MINX(
FILTER(Sales,Sales[Productname]=EARLIER(Sales[Productname]))
,Sales[SalesDate])

So what does this formula do ? Give me the MIN value of Sales[SalesDate] for all the rows in the Sales table where the Sales[Productname] is the same value as the Sales[Productname] from the current row.

The function to note here is EARLIER(). What does this do ? well FILTER(Sales) gives us a filter over the entire Sales table, we no longer are aware of the current row of the table we are in, the earlier goes to the previous row context, being the current row of the table we are running the formula of the calculate column in.

This results in the following value for each row:

As you can see we now have the first date for each product available. Now all we have to do is calculate the difference between the dates in months. Unfortunately DAX doesn’t have a datediff function so we have to do it the Excel way, I am using a technique described in a blog post from Vidas Matelis for it.

= (YEAR([SalesDate]) - YEAR([First sales date]) ) *12
+ (MONTH([SalesDate])- MONTH([First sales date]))

This results in the column that gives us the months for each sales transaction compared to the date of introduction

Now we can compare the sales from these products in a pivottable:

But this of course is not the end of it, we now only see the sales for each month. We would like to see the sales since product introduction to the current date.

This is pretty simple formula:

=if(HASONEVALUE(Sales[MonthSinceIntroduction]),  CALCULATE([Sum of Sales], Sales[MonthSinceIntroduction] <= VALUES(Sales[MonthSinceIntroduction])) , BLANK())

This formula will check if only one month is selected in the current context, and for this single month calculate the [Sum of Sales] where the MonthSinceIntroduction is smaller or equal to the current MonthSinceIntroduction value we are in on the column.

This will result in this pivottable:

But hey.. hold on..why don’t we see values for the months we don’t have sales ? The dax formula should work. I had to dig deep to find an answer and had to call in the troops (called Jeffrey Wang in this case).

This behavior is a caused by the MDX query that is being executed. Remember the data of the Pivottable is being queries by MDX. MDX will take care of retrieving the rows, column and filters, after it has retrieved the entire. Each DAX measure is being evaluated for each cell (or the cross section between rows, column and filters). One of the behaviors that MDX has  is that when there is no value in the table for a row / column combinations from the same table MDX Autoexists kicks in. Autoexists will make sure that if the combination between rows, columns and filters does not exists within the same table it will NOT execute the formula for that spot. Since MDX is designed for a Multidimensional model where for each fact table you have a separate dimension this made sense. For PowerPivot this makes a little less sense but still pretty simple to work around.

1 Use Crescent that will use DAX to create the report and does not have MDX autoexist.

2 create a second table with values 0 to 25 in it and link it to the original table and use those values in the report. That will solve the problem:

Observe I did not change the formula at all.

Now I can create a chart for it as well:

Something that might be interesting to know is that DATEADD and PARALLELPERIOD  look very similar but do behave differently. This blog posts looks at the difference between the two. Lets look at these two formula’s:

=CALCULATE([Sum of SalesAmount], DATEADD(DateTable[FullDateAlternateKey],-1,YEAR))
=CALCULATE([Sum of SalesAmount], PARALLELPERIOD(DateTable[FullDateAlternateKey],-1,YEAR))

They both look very similar right ? But what is the difference ? Lets look at the results:

The first and only thing you see here is the difference of 2004. Why ? To determine previous year DATEADD uses a range between the first date where you have values in the facttable to the last date where you have values in your facttable for the current year on rows, it will this range to go back one year. Whereas  PARALLELPERIOD uses the first and last date in your datetable for the current year to go back one year.

What ? Ok let’s look at it in a table for DATEADD :

I added two measures that will show you what the first and last date are that being used to go back one year. For 2004 you can see that it uses the startdate 1/1/2004 as this was the first date a sale was made in that year. But as last year we see 8/31/2004 because this was the last date a sale was done in that year (the year is not finished yet). If you now go back one year it will calculate the sum of SalesAmount for 1/1/2003 to 8/31/2003.

Now look at the first and lastdates PARALLELPERIOD uses. As you can see the it determines the current year, looks for the first and last day in the year using the date table   and use that to move back one year. Going back one year it will calculate the sum of SalesAmount for 1/1/2003 to 12/31/2003. Giving a larger result.

Ok looks like PARALLELPERIOD is the way to go right ? Well it depends on what you want to do. PARALLELPERIOD really goes back one entire year (based on what you select in the parameter) using the first and last date. DateAdd uses the first and last date in the current selection. What does this mean ? We can best illustrate this by adding months into the mix:

What makes these two measures return different results here is that both use a different mechanism to determine the current period. Where PARALLELPERIOD always goes back one entire year (or whatever parameter you select in the measure), DATEADD will look at the current row selection and go back one year for that selection. In this final image for the row with month 04/2004 DATEADD will show you the sum of SalesAmount for 04/2003 where PARALLELPERIOD will go back one entire year.

In Denali we made a small (but significant) change to the Find and Search syntax. By adding a new optional argument “NotFoundValue” you can tell DAX to return a value instead of returning an Error when the value is not found. In 2008 R2 DAX would always return a Error.

What is the benefit ? When you use the find or search function in a calculated column and it will return an Error for one row it will return an Error for all the rows. To solve this you have to wrap the Find or Search formula with a =iferror() to actually be able to work with this formula. Another benefit is that you don’t have to work with errors inside the function, this can degrade performance.

With the release of Tabular model / PowerPivot we have introduced DAX functions that work with a Parent Child dimension. In this blog post we will show you how to use these functions to set up a parent child relationships and using it in a hierarchy.

Read more…

In Denali we introduced a new function to RANK a column based on a measure. In this blog post I will show you how to use the RANKX function to rank the values of a measure.

Read more…

In the SQL server Denali release it will be possible to work with multiple relationships between two tables. This blog post will show you how to use this in DAX to create a measure that uses the inactive relationship.

Read more…

In Denali a couple of new functions are introduced that will make your DAX writing life a little easier. This blog post will cover the most interesting new functions that are available in Denali.

The first one that I want to cover is DistinctCount(), lets say we want to count the distinct number of customers for a given year we only have to do a formula like this:

=DISTINCTCOUNT(Sales[CustomerKey])

as easy as that

Another great improvement is the HASONEVALUE function. Lets say you want to do a running total over all the year. That would be a measure like this:

=CALCULATE([Sum of SalesAmount], DateTable[CalendarYear] <= VALUES(DateTable[CalendarYear]))

But putting this in a pivottable would give you a error:

ERROR – CALCULATION ABORTED: Calculation error in measure ‘DateTable’[Measure 1]: A table of multiple values was supplied where a single value was expected.

So you need to wrap a check around this function to make sure that this function is only executed for a single year and not for the grand total, that would look like this:

=if(COUNTROWS(VALUES(DateTable[CalendarYear])) =1,

CALCULATE([Sum of SalesAmount], DateTable[CalendarYear] <= VALUES(DateTable[CalendarYear])), BLANK())

In denali we can write it like this:

=if(HASONEVALUE(DateTable[CalendarYear]),

CALCULATE([Sum of SalesAmount], DateTable[CalendarYear] <= VALUES(DateTable[CalendarYear])), BLANK())

In Denali the Search function has an extra parameter that let’s you return a value if nothing is found. In 2008 R2 if no value is found the function will throw an error, which makes this function not easy to work with.

Now you can just do this:

=SEARCH(“Dollar”,Currency[CurrencyName],1,-1)

It will return -1 if no string is found that matched “Dollar”.

Another function that will make you life easier when writing DAX is the Switch function. Let say you want to have a measure that behaves differently for each productgroup. In 2008 R2 you had to write:

=if(values(category[type]) = 1,sum(Sales[Amount]) * 2,
if(values(category[type]) =2, sum(Sales[Amount]) * 4,
if(values(category[type])=3, sum(Sales[Amount]) * 6,
sum(Sales[Amount])))

In Denali we can write the following:

=Switch(values(category[type]),
1, sum(Sales[Amount]) * 2,
2, sum(Sales[Amount]) * 4,
3, sum(Sales[Amount]) * 6,
sum(Sales[Amount]))

Much simpler and easier to read.

Keep posted for more new DAX functions and Denali tips and tricks to come in next weeks.