Forum Discussion

vsatamraju's avatar
vsatamraju
Microsoft Employee
10 years ago

Creating a Hierarchy in Power BI

Hi All,   We are Clustered Column Chart in Power BI. We are implementing the drill down feature for employee heirarchy from top to bottom according to the Organization depth. Now we want to show t...
Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
10 years ago

vsatamraju

 

Suppose the dataset is as the 3 columns highlighted. Create a calculated column [path] and table [calculatedTable] as below. Then use a measure subordinates in the chart.


path = PATH(Employees[Name],Employees[Parent])

CalculatedTable =
FILTER (
    CROSSJOIN (
        FILTER (
            ALL ( Employees[Name], Employees[Title] ),
            Employees[Title] = "manager"
        ),
        ALL ( Employees[path] )
    ),
    PATHCONTAINS ( Employees[path], Employees[Name] )
)

subordinates = COUNTA(CalculatedTable[path])-1

You can check more details in the attached pbix. Also check Understanding Functions for Parent-Child Hierarchies in DAX.

 

If it solves your problem, please accept it as solution. For any question, feel free to let me know.

rebin40's avatar
rebin40
Regular Visitor
8 years ago

Hi Eric, 

 

Thank you for this interesting piece of code; however, where do you enter the code for calculatedtable?