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.
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.
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.
One of the subtle changes made to PowerPivot with Denali is that it now supports drill-through actions in Excel. In SQL Server 2008 R2 PowerPivot we get the following message:
In Denali PowerPivot supports drill-trough in Pivottables. Let’s say I have the following Pivottable where I want to see the underlying rows of data that make up this sum of salesamount:
I can double click on the measure and it will open a new sheet in Excel that shows you the underlying rows of data in the fact table:
Notice that the rows are filtered by the values that combine the measure that we clicked on.
By default the drill-through will return only the first 1000 rows, but this is configurable in the connection, go to Excel, Data, Connections:
Select the PowerPivot Data connection, this is the connection that is made to the in memory, internal SSAS engine that runs inside PowerPivot.
Press properties:
In this dialog you can change the number or records you want to retrieve in the Drill through.
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.
Time Intelligence functions are not new in SQL Server Denali. But in Denali we have made working with these functions a little easier and flexible. To use these one of these functions in 2008 R2 DAX you used to apply some rules: http://www.powerpivotblog.nl/powerpivot-time-intelligent-functions-golden-rules.
The rules in SQL 2008 R2 were the following:
With the release of Denali we have changed these to the following:
So what is changed here? In Denali your date table can have a column that is related to the fact table that is not of the type Date. This is a situation most datawarehouses will have set up. Most data warehouses will have a fact table that is joinedwith a date table based on a column that is an integer value. In 2008 R2 PowerPivot getting this to work was very hard.
In Denali all we have to do in PowerPivot / BIDS is make sure that our model knows which table is a date table and what column is a date column. The reason we need to supply DAX with a date column is that DAX needs to know how to apply its time intelligence functions, if you have an integer column like 20031010 how does DAX then know how to subtract a year?
Open the PowerPivot window, select the date table, go to design and click Mark as Date Table:
This will prompt you a dialog. In this dialog you can select the column that contains your date values:
This will result in two things:
As you can see this small operation has big effects
Finally the long wait (for me at least) is over. As of today you can download the CTP3 PowerPivot and BIDS. CTP means Community Technology Preview meaning that this is a preview of the final product. But it is very rich in features ! I will do multiple blogpost over the next weeks explaining a lot of the new features.
My collegue Kay Unkroth has created a great blogpost summarizing the release which you can find here. Another big part of this release is Project Crescent which uses PowerPivot and Tabular projects as source. Read this blog post by the RS team for more information about Crescent.
Download PowerPivot 64 bits here.
Download SSAS server including the tabular service, BIDS and Crescent here.
Have fun with the CTP and let me know what you think ! Ow and most of all don’t forget to file bugs / suggestions on connect if you want your voice to be heard by the product team, see here why and how. To directly go to the SQL server connect page go here: https://connect.microsoft.com/SQLServer/Feedback and add your feedback.
