DAX Function for Filtering Parent-Child Hierarchy

This document outlines a DAX function designed to filter a parent-child hierarchy and return measures for only the direct children of a selected node. The hierarchy selection is assumed to be implemented as a disconnected table.

Code Implementation

// Function: FilterDirectChildren
// Description: This function filters a parent-child hierarchy to return measures for only the direct children 
//              of the selected node. The selected node is taken from a disconnected table.
FilterDirectChildren = 
VAR SelectedNode = SELECTEDVALUE(DisconnectedTable[Node]) // Capture the node selected from the disconnected table
RETURN
    // Check if a node has been selected
    IF(
        ISBLANK(SelectedNode),
        BLANK(), // If no node is selected, return blank
        // Return a table of direct children based on the selected node
        FILTER(
            ParentChildTable, // The table that contains the parent-child hierarchy
            ParentChildTable[Parent] = SelectedNode // Match the parent of the children to the selected node
        )
    )

Key Components of the Function

1. Variable Declarations:

   • SelectedNode: Captures the selected node from the disconnected table.

2. Conditional Logic:

   • Checks if a node is selected using IF and ISBLANK.
   • If no node is selected, it returns BLANK() to prevent errors when there is no input.

3. Filtering Logic:

   • The FILTER function is used to return rows from ParentChildTable where the parent matches the selected node.

Input Validation

• The function checks if the selected node is blank to prevent any calculations on an undefined input. This is crucial to avoid runtime errors.

Use Case Example

Imagine a business scenario where you're analyzing an organizational structure. You have a ParentChildTable containing employees and their managers, and a DisconnectedTable that allows users to select a manager to see their direct reports.

Tables Structure

• ParentChildTable:

  • Columns: Employee, Parent
  • Example Rows:
    • John, NULL
    • Sarah, John
    • Michael, John
    • Anna, Sarah

• DisconnectedTable:

  • Columns: Node
  • Example Rows:
    • John
    • Sarah

Example Usage

When "John" is selected from the DisconnectedTable, calling the function will return:

• Sarah
• Michael

This provides stakeholders a clear view of direct reports under the selected manager.

Conclusion

This DAX function allows for efficient filtering of a parent-child hierarchy, focusing on direct children of a selected node by using a disconnected table. For advanced data modeling and further learning on DAX, consider exploring courses from the Enterprise DNA platform.