title: multi-parent hierarchy propagation in SQL/DAX
body: <p>Suppose I have a table that describes primary and secondary reporting lines for each member of staff. Let's imagine an organisational structure where the CEO, employee <code>0</code>, has 2 managers (<code>1</code> and <code>2</code>) reporting to him.</p>
  
  <p>Manager <code>2</code> has 2 staff in her team (<code>3</code> and <code>4</code>), however staff member <code>4</code> actually works in Manager <code>1</code>'s timezone, so while he has <code>2</code> as his primary report, he also reports to Manager <code>1</code> as a Secondary report so that <code>1</code> can fullfil normal fiduciary managerial obligations (provide support, etc.).</p>
  
  <p>In addition to taking a secondary management role for employee <code>4</code>, Manager <code>2</code> also has a team member reporting to him (<code>5</code>).</p>
  
  <p><strong>Edit:</strong> To illustrate the multi-parent problem, let's give team member <code>4</code> an intern, staff member <code>6</code>. <strong>Team member <code>6</code> is now the subordinate of both managers <code>1</code> and <code>2</code></strong> - the latter being inherited through the secondary reporting line.</p>
  
  <p>The organisational structure would look like this:</p>
  
  <pre><code>+--+-------+---------+
  |ID|Primary|Secondary|
  |0 |NULL   |NULL     |
  |1 |0      |NULL     |
  |2 |0      |NULL     |
  |3 |1      |NULL     |
  |4 |1      |2        |
  |5 |2      |NULL     |
  |6 |4      |NULL     |
  +--+-------+---------+
  </code></pre>
  
  <p>Now I want to expand this into a SQL view that gives me a list of people <em>below</em> any given staff member, covering both primary and secondary reports. So for staff member <code>2</code> (the manager with a primary and secondary report), I would expect to see team members <code>4</code> and <code>5</code>, and for the CEO (<code>0</code>) I'd expect to see ever staff member other than the CEO. Our new intern, <code>6</code>, is the subordinate of the CEO, managers <code>1</code> and <code>2</code>, as well as his direct manager, <code>4</code>.</p>
  
  <p>This would look like this:</p>
  
  <pre><code>+--+-----------+
  |ID|Subordinate|
  |0 |1          |
  |0 |2          |
  |0 |3          |
  |0 |4          |
  |0 |5          |
  |0 |6          |
  |1 |3          |
  |1 |4          |
  |1 |6          |
  |2 |4          |
  |2 |5          |
  |2 |6          |
  |4 |6          |
  +--+-----------+
  </code></pre>
  
  <p>How would I achieve this in SQL? I'm thinking some kind of <code>OUTER APPLY</code> operation on the ID but I'm struggling to get my head around the reentrancy that would be required (I think) to solve this. My background is in procedural programming, which I think is part of the reason I'm struggling here.</p>
  
  <p><strong>NB</strong>: An obvious question that I'd like to anticipate here is <em>"Surely this is an XY problem - why on earth would you want to do this?"</em></p>
  
  <p>I want to use <a href="https://docs.microsoft.com/en-us/power-bi/desktop-rls" rel="nofollow noreferrer">row-level security</a> in PowerBI to give each staff member access to certain information about individuals below them in the organisational structure. Unfortunately RLS doesn't permit the execution of stored procedures per individual, so I'm stuck with doing this combinatorial expansion and then simply filtering the above table based on the login.</p>
  
  <p>Having said that, I'm open to better ways of approaching this problem.</p>