How to create a supervisor hierarchy table:

[BBHouston]

Let's say I have a table that looks like this:

Staff Name	Supervisor
Ryan	*blank*
Martha	Ryan
Erica	Ryan
David	Martha
Samantha	Erica
Kyle	Erica

How do I build a table or table visual that looks like this? 

Lv. 1	Lv. 2	Lv. 3
Ryan	Erica	Samantha
		Kyle
	Martha	Ryan

[v-easonf-msft · Accepted solution]

Hi， @BBHouston 

You  also can try  a solution in Dax.

Path = PATH('Dax'[Staff Name],'Dax'[Supervisor])

Level1 = PATHITEM('Dax'[Path],1)

Level2 = PATHITEM('Dax'[Path],2)

Level3 = PATHITEM('Dax'[Path],3)

 

Best Regards,
Community Support Team _ Eason

[BBHouston]

@v-easonf-msft Thank you!!! This solution ended up being the simplest and working the best for me. 

[mahoneypat]

Please see this article on how to use the PATH function to do this.

On the Right PATH() - A DAX/Power Query approach to working with parent/child hierarchies. - P3 (pow...

Regards,

Pat

[CNENFRNL]

Hi, @BBHouston , you might want to try a solution in Power Query

let

    Path = (emp) as text => 
        let
            sup = suplist{List.PositionOf(emplist, emp, Occurrence.First)},
            recursion = if List.PositionOf(emplist, sup) = -1 then "" else @Path(sup) & "|" & sup
        in
            recursion,

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCqpMzFPSUVKK1YlW8k0sKslIBPLAgiAR16LMZBQBl8SyzBSgAFQpSCg4MTcxD6IPohwk6F2Zk4oQiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Staff Name" = _t, Supervisor = _t]),
    emplist = List.Buffer(Source[Staff Name]),
    suplist = List.Buffer(Source[Supervisor]),

    #"Added Hierarchy" = Table.AddColumn(Source, "Lv", each Path([Staff Name]) & "|" & [Staff Name]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Hierarchy", "Lv", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Lv0", "Lv1", "Lv2", "Lv3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Staff Name", "Supervisor", "Lv0"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Lv3] <> null))
in
    #"Filtered Rows"