Denali PowerPivot: new DAX functions that will make your life easier

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.