DAX Hierarchy Measure

Modified on Sun, 26 Jul at 4:33 AM

Overview

QQube Hierarchy Measures begin with [DAXP and are hidden from normal report-building views.

They perform reusable technical calculations that handle ragged hierarchies in financial statement groupings and account levels.

Why Hierarchy Measures Are Needed

When users drill into a Level 1 account, some accounts may have subaccounts at Level 2 or deeper while others do not. Hierarchy Measures prevent blank rows from appearing for accounts that have no lower-level children.

Financial statement hierarchy without blank rows at unused account levels

No Presentation Cleanup Required

The hierarchy logic removes the need for the separate Presentation Cleanup mechanism used by the QQube Excel Add-In.

Where Hierarchy Measures Are Used

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)

Additional Data Models

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

Supporting Hierarchy Measures

Measure	Purpose
[DAXP] AccountRowDepth	Finds the maximum account level represented by the current row.
[DAXP] AccountBrowseDepth	Uses nested IF logic to determine which hierarchy level is active in the visualization or analytic.

DAX Example

Both supporting measures are used in the following .Hierarchy Actual measure from 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

How the Measure Works

Calculate the Signed Financial Value

The measure first sums Actual Amount and applies the account signage required for the financial statement.

Determine the Active Depth

AccountBrowseDepth identifies the hierarchy level currently expanded in the visual, while AccountRowDepth identifies the level represented by the current row.

Identify Leaf Accounts

The Account Has Children field determines whether the current account is a leaf account with no lower-level children.

Return Only Appropriate Values

The SWITCH logic returns the financial value only when the current hierarchy depth is appropriate, preventing blank or misleading rows at unused account levels.

Expected Result

Hierarchy Measures allow financial statements and related analytics to expand and collapse across uneven account structures without displaying blank rows for hierarchy levels that do not exist.