DAX Hierarchy Measure

Modified on Sat, 29 Jun at 2:40 AM

Overview

These start with [DAXP and are hidden. Their purpose is to perform functions to handle ragged hierarchies in financial statement groupings and account levels.

For instance, if you drill down on a level 1 account, and some of those have sub-accounts at level 2 or greater, you won't get blank rows where there is NOT level 2 or greater sub-accounts.

These negate the need for a mechanism such as used in our Excel Add-In - the Presentation Cleanup.

Where Used

They are used in the following Data Models:

Jobs, Time and Mileage
- Profit and Loss by Job (Flexible Period)
- Profit and Loss by Job by Class (Flexible Period)
Financial Statements
- Balance Sheet (Standard Monthly Period)
- Balance Sheet (Flexible Period)
- Profit and Loss (Standard Monthly Period)
- Profit and Loss (Flexible Period)
Financial Statements by Class
- Balance Sheet (Standard Monthly Period)
- Profit and Loss (Standard Monthly Period)
- Profit and Loss (Flexible Period)
52/53 Periods Profit and Loss
52/53 Periods Profit and Loss by Class
Trial Balance - Current
Trial Balance Activity
Trial Balance Activity by Class
Profit and Loss Detail

There are two formulas:

[DAXP] AccountRowDepth Finds the maximum level.
[DAXP] AccountBrowseDepth Nested IF statements to determine what level is being activated in the visualization or analytic.

Example

Both of these measures are used in this ".Hierarchy Actual" Formula in the Flexible Period Profit and Loss Data Model

.Hierarchy Actual =
VAR ProfitAndLossActual =
  SUM ( 'PROFIT AND LOSS MEASURES'[Actual Amount] )
  * MAX ( 'Account'[Sign To Apply] )
RETURN
  VAR AccountBrowseDepth = [[DAXP]] AccountBrowseDepth]
  VAR AccountRowDepth = [[DAXP]] AccountRowDepth]
  VAR _IsLeaf =
  IF (
      HASONEVALUE ( Account[Account Has Children] ),
      VALUES ( Account[Account Has Children] )
  )
  VAR Result =
  SWITCH (
      TRUE (),
      AccountBrowseDepth = AccountRowDepth + 1
      && NOT ( ISBLANK ( ProfitAndLossActual ) )
          && _IsLeaf = TRUE (), ProfitAndLossActual,
      AccountBrowseDepth <= AccountRowDepth, ProfitAndLossActual
  )
  RETURN
  Result