Rank column in Power BI that change dynamically

Question

Trying to create Organization Chart using custom visuals in Power BI Desktop, the requirement is to show chart with filtered data which applies by Slicer on department column.

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 ReportsTo column should be null to identify as root node.

Managed to make Reportsto column null in each department within SQL view using ROW_NUMBER () OVER (PARTITION BY EmpDepartment Order by EmpGrade desc and CASE 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.

I tried following DAX in Power BI desktop to get the RANK based on Grade with filtered data, Unfortunately, the DAX work as Measure not as Calculated Column, and With the measure i'm unable to get expected result when tried a calculated column using IF([RANK]=1,BLANK(), ('Table'[ReportsTo]))

Note: Index column added in Power BI Desktop - Edit Queries page

RANK = 
MINX (
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ('Table'),
            "index", 'Table'[Index],
            "rank", RANKX ( ALLSELECTED ( 'Table' ), 'Table'[Grade],, DESC, DENSE )
        ),
        [index] = MAX ( 'Table'[Index] )
    ),
    [rank]
    )
)

Here is sample data, as per following table when department filtered with IT (by slicer), EmpID 107 would be Root Node based on ReportsTo_2 column. But visual won't work when 2 departments selected.

+-----------+------------+-------+---------------+---------+--------------+
| 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     |
+-----------+------------+-------+---------------+---------+--------------+

Expected Result: My idea is to get null value in ReportsTo_2 column for highest Grade within filtered data (filter by department).

Appreciated your advise to achieve this. Thanks

Additional info for clarification as requested in comments

Using following visuals:
Custom Visual 1
Custom Visual 2

Scenarios:

When filtered with multiple departments (No Root Node available), if i use ReportsTo_2 as parent it show the error "multiple Root Nodes": Image Link
When NO filter applied in departments: Image Link

Same Scenarios with Custom Visual 2:

Additional Note 2:
Main issue where i struck with, as Measure we can easily get expected result, but as Calculated Column same expression doesn't give expected result.
Image Link

Measures can work dynamically, calculated columns not. So you say the visual crashes, is that not your real problem? — Aldert
I agree, but in this case I don't think so, as long as the filtered data-set has root node the visual works. It this point i'm not looking forward to a fix in visual. — Shekar Kola
Can you show what would be your desired result if both the IT and Proc. departments are selected? I'm unable to derive it not knowing the visual you are trying to use. Sounds like a solvable problem though. — Jelle Hoekstra
Did you try this on measure: RANKX ( ALLSELECTED ( Table ), Table[Grade],, DESC, DENSE ) Columns can't be dynamic. You can use a measure like this on a visualization like table to simulate a column. — ibarrau
Dear @Nacho I already tried this, but that would give me the RANK based on entire table, requirement is to get the RAN for only filtered data, and use the RANK to in IF condition to replace highest grade to null — Shekar Kola

Jelle Hoekstra Jelle Hoekstra · Accepted Answer · 2019-08-06T08:14:42

I think I understand your problem, the measure your need is below. However please note the limitations of your visuals. They both explicitly need one starting node. Multiple or no starting nodes are not allowed. So even with this measure, selecting both the IT and Proc. in your filter will result in an error as it gives both visuals two possible starting nodes. This measure will solve the problem of having no starting node as it will always result in at least one blank value in your ReportsTo_2.

You can replace the "" with BLANK() when this is explicitly needed, I didn't test it on the visuals.

ReportsTo_2 =

var _highestSelectedRank = CALCULATE( MAX ( 'Table'[Grade] ) ; ALLSELECTED( 'Table' ) )

RETURN
    IF (
        MAX( 'Table'[Grade] ) = _highestSelectedRank ;
        "" ;
        MAX ( 'Table'[ReportsTo] )
    )

Update based on comments: Or forcing the measure to behave like a calculated column

ReportsTo_2 =

var _highestSelectedRank = CALCULATE( MAX ( 'Table'[Grade] ) ; ALLSELECTED( 'Table' ) )

RETURN
    MAXX (
        'Table' ; 
        IF (
            'Table'[Grade] = _highestSelectedRank ;
            "" ;
            'Table'[ReportsTo]
        )
    )

Rank column in Power BI that change dynamically

1 Answers