title: Rank column in Power BI that change dynamically
body: <p>Trying to create Organization Chart using <strong>custom visuals</strong> in Power BI Desktop, the requirement is to show chart with filtered data which applies by <strong>Slicer</strong> on department column. </p>
  
  <p>I struck with the limitation of custom visual (which logical), to get proper chart in visual, data-set must have a Root Node, In my scenario <code>ReportsTo</code> column should be <code>null</code> to identify as root node.</p>
  
  <p>Managed to make <code>Reportsto</code> column <code>null</code> in each department within SQL view using <code>ROW_NUMBER () OVER (PARTITION BY EmpDepartment Order by EmpGrade desc</code> and <code>CASE</code> logic. But this already processed data, i'm looking for something during filter selection, so that when user select 2 departments in slicer custom visual won't crash. </p>
  
  <p>I tried following DAX in Power BI desktop to get the RANK based on <code>Grade</code> with filtered data, Unfortunately, the <strong>DAX</strong> work as <strong>Measure</strong> not as <strong>Calculated Column</strong>, and With the measure i'm unable to get <strong>expected result</strong> when tried a calculated column using <code>IF([RANK]=1,BLANK(), ('Table'[ReportsTo]))</code>   </p>
  
  <p><em>Note: Index column added in Power BI Desktop - Edit Queries page</em></p>
  
  <pre><code>RANK = 
  MINX (
      FILTER (
          SELECTCOLUMNS (
              ALLSELECTED ('Table'),
              "index", 'Table'[Index],
              "rank", RANKX ( ALLSELECTED ( 'Table' ), 'Table'[Grade],, DESC, DENSE )
          ),
          [index] = MAX ( 'Table'[Index] )
      ),
      [rank]
      )
  )
  </code></pre>
  
  <p>Here is sample data, as per following table when department filtered with <strong>IT</strong> (by slicer), <code>EmpID</code> <strong>107</strong> would be Root Node based on <code>ReportsTo_2</code> column. But visual won't work when 2 departments selected. </p>
  
  <pre><code>+-----------+------------+-------+---------------+---------+--------------+
  | EmpID     | ReportsTo  | Grade | EmpDepartment | EmpName | ReportsTo_2
  +-----------+------------+-------+---------------+---------+--------------+
  | 101       | 107        | A1    | IT            | Emp1    | 107         
  | 102       | 107        | A1    | IT            | Emp2    | 107        
  | 103       | 107        | A1    | IT            | Emp3    | 107        
  | 104       | 108        | A2    | Proc.         | Emp1    | 108        
  | 105       | 108        | A2    | Proc.         | Emp1    | 108        
  | 106       | 108        | A2    | Proc.         | Emp1    | 108        
  | 107       | 109        | B1    |  IT           | Mgr1    |
  | 108       | 109        | B1    | Proc.         | Mgr2    |
  | 109       | 110        | C2    | Management    | Director|
  | 110       |            | D9    | Sr.Management | CEO     |
  +-----------+------------+-------+---------------+---------+--------------+
  </code></pre>
  
  <p><strong>Expected Result:</strong> My idea is to get <code>null</code> value in <code>ReportsTo_2</code> column for highest <code>Grade</code> within filtered data (filter by department). </p>
  
  <p>Appreciated your advise to achieve this. Thanks</p>
  
  <p><strong>Additional info for clarification as requested in comments</strong>   </p>
  
  <p>Using following visuals:<br>
  <a href="https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381333?tab=Overview" rel="nofollow noreferrer">Custom Visual 1</a><br>
  <a href="https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381630?src=office&amp;tab=Overview" rel="nofollow noreferrer">Custom Visual 2</a>  </p>
  
  <p>Scenarios:  </p>
  
  <ol>
  <li>When filtered with multiple departments (No Root Node available), if i use <code>ReportsTo_2</code> as parent it show the error "multiple Root Nodes": <a href="https://i.stack.imgur.com/gSz8N.jpg" rel="nofollow noreferrer">Image Link</a></li>
  <li>When NO filter applied in departments: <a href="https://i.stack.imgur.com/h5J7x.jpg" rel="nofollow noreferrer">Image Link</a></li>
  </ol>
  
  <p>Same Scenarios with Custom Visual 2:</p>
  
  <ol>
  <li><a href="https://i.stack.imgur.com/k8njo.jpg" rel="nofollow noreferrer">Image Link</a></li>
  <li><a href="https://i.stack.imgur.com/FXZFK.jpg" rel="nofollow noreferrer">Image Link</a></li>
  </ol>
  
  <p><strong>Additional Note 2:</strong><br>
  Main issue where i struck with, as <strong>Measure</strong> we can easily get expected result, but as <strong>Calculated Column</strong> same expression doesn't give expected result.<br>
  <a href="https://i.stack.imgur.com/rUpMS.jpg" rel="nofollow noreferrer">Image Link</a></p>