HR People RollUp - How to search across columns?

Power BI
3

@chad.sharpe,

Super interesting problem. I can see why this gave you trouble - definitely not an easy one to crack.

Here’s the general approach I took:

POWER QUERY

  • Added an index called Path Num to the source table
  • Created a Full Path Parent-Child Hierarchy by merging columns L8 through L4 with “|” as the delimiter, with some additional text processing to remove the extra delimiters around null columns
  • Unpivoted columns L8 through L4
  • Used GroupBy and Table.AddIndexColumn to assign an item number representing the position # in each path of every name in that path

DAX

Created a disconnected table slicer on employee name
Created a measure called InPath, which had a value of 1 if the selected employee from the disconnected slicer was in the full path, 0 otherwise
Created a measure called MaxSubordinate which calculated the Item Num for the slicer selected name within each path
Created a measure called Include which got assigned 1 if both of the following conditions were true, 0 otherwise:

[InPath] = 1 AND
SELECTEDVALUE( ‘People RollUp’[Item Num] ) > [MaxSubordinate]

Finally, created a measure called Reportees that like the rug in The Big Lebowski, tied the whole room together:

Reportees = 
VAR vTable =
    FILTER( 'People RollUp', [Include] = 1 )
VAR DistinctReports =
    DISTINCT( SELECTCOLUMNS( vTable, "@Name", 'People RollUp'[Name] ) )
VAR Result =
    CONCATENATEX( DistinctReports, [@Name], UNICHAR( 10 ), [@Name], ASC )
RETURN
    COALESCE( Result, "None" )

Here’s the whole thing in action:

image
image981×543 37.6 KB

I hope this is helpful. Full solution file posted below.

4 Likes