Recently when browsing the MSDN PowerPivot Forums I came across this thread where the question was ask on how to show the value which is directly linked to an element in a parent-child hierarchy instead of the aggregation of all "children". In this post I am going to address this problem finally showing a proper solution.
First of all I would like to start with some background to so called "datamembers". The term "datamember" originates from parent-child hierarchies in multidimensional models. It is a dummy-member that is created automatically below each hierarchy-node to hold values that are linked to a node-member directly. This is of course only possible for parent-child hierarchies.
Take the following MDX-Query for example:
[Measures].[Sales Amount Quota] ON 0,
Descendants(
[Employee].[Employees].&[290],
1,
SELF_AND_BEFORE) ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].&[2006]
As we can see, the value of "Amy E. Alberts" is not the Sum of its children but has a difference of $108,000.00 This difference is because of the "datamember" that is also a child of every parent-child node which may be hidden. For multidimensional models his behavior can be changed by setting the MembersWithData-Property to "NonLeafDataVisible":
Executing the same query again we now get this results:
Now we see "Amy E. Alberts" twice – the node and the datamember.
Doing a similar query on a tabular model we will get this result by default:
As you can see, we still have the datamember but without any caption. Though, this can of course be changed when you flatten out the hierarchy using DAX by simply replacing BLANK() by "<my datamember caption>" in your calculated columns.
Anyway, this is not the topic of this post but just some background information. Now we will focus on how to query data that is directly associated to a node or its corresponding datamember in MDX and DAX.
MDX:
For MDX this is quite easy as the datamember can be referenced by simply using ".datamember" of a given hierarchy node:
MEMBER [Measures].[DataMember] AS (
[Measures].[Sales Amount Quota],
[Employee].[Employees].currentmember.datamember
), FORMAT_STRING = 'Currency'
SELECT
{
[Measures].[Sales Amount Quota],
[Measures].[DataMember]
} ON 0,
Descendants(
[Employee].[Employees].&[290],
1,
SELF_AND_BEFORE) ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].&[2006]
The DataMember-Measure only shows values that are directly linked to the current node of the parent-child hierarchy. This is pretty straight forward and very easy to accomplish.
DAX:
In this DAX this problem is a bit more tricky as we do not have that built-in ".datamember" function. Also navigation within hierarchies and parent-child hierarchies itself are not really supported in DAX. Though, there are several blogs out there that describe how to handle parent-child hierarchies by Alberto Ferrari (here) and Kasper de Jonge (here). Based on these solutions we can create our datamember-calculation.
First (and only) thing we need is to add a calculated column that stores the path depth of the current row. This can be achieved by using this formula:
PATH(
Employee[EmployeeKey],
Employee[ParentEmployeeKey]
)
)
Using our previous example and selecting "Amy E. Alberts" together with our new column we get this:
As we can see there are two different Levels below "Amy E. Alberts" where Level=3 holds our datamember-value and Level=4 holds all the other values (=real child members). Lets add a calculated measure to help you make the final calculation more understandable:
So, for our [DataMember]-calculation we simply have to extend the context to only include rows where [Level] = [MinLevel]
SUM('Sales Quota'[Sales Amount Quota]),
FILTER('Employee',
'Employee'[Level] = [MinLevel]))
Well, obviously our calculation is not returning the expected results. This is because of a very common pitfall which I am also very prone to. Whenever a calculated measure is used within an other calculated measure, a CALCULATE() gets wrapped around it thereby taking over the current context. In our case this means that ‘Employee'[Level] will always be the same as [MinLevel] as [MinLevel] gets evaluated for each row and doing MIN() on only one row of course always returns the value of the current row which we compared it to!
To solve this issue we have to place the original [MinLevel]-calculation directly into our [DataMember]-calculation:
SUM('Sales Quota'[Sales Amount Quota]),
FILTER('Employee',
'Employee'[Level] = MIN(Employee[Level])))
This change finally makes the calculation return the correct results also when browsing using the hierarchy:
Even though DAX is not very good in handling hierarchies, the [DataMember]-calculation can be accomplished quite easily. This calculation also works for hierarchies of any depth without needing to adopt it. In terms of performance it may be further improved to the following formula – though the impact will be minor:
SUM('Sales Quota'[Sales Amount Quota]),
FILTER(VALUES('Employee'[Level]),
'Employee'[Level] = MIN(Employee[Level])))
Download Final Model (Office 2013!)