Direct and Indirect Report

[Anonymous]

Hello PowerBi Community.. I am new to this tool and thought could get your help and expertise with Direct and Indirect reports. I found quite a few links on this topic but could not help achieve what I was looking for. Attached is the excel and Power Bi report with the data. All I am looking for is if I select a Manager, it should display the total reports (Direct+ Indirect), direct report and Indirect report. Any help on this would be greatly appreciated. 

Thank you!

Here is the link to the file and the report.https://drive.google.com/drive/folders/1335DTWOxzHtXj8CplBzFffEwYyib0Pye?usp=sharing 

[Anonymous]

Perfect! Exactly what I wanted. Thank you!

[Anonymous]

Hi @Anonymous ,

Have a goody day. If the file contains sensitive data, please try sharing sample data and expected results.

Best Regards,

Wearsky

[Anonymous]

Hello.. Sorry about that. Shared a new link. Thank you!

[lbendlin]

please check the link, it asks for credentials

[Anonymous]

Hello.. Sorry about that. Shared a new link. Thank you!

[lbendlin]

Change your source data to make it usable.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZJfb4IwFMW/iuH5PFj8s/nIptk0wyzqGzHmCjdiBq0pZIvffmy0liq8wDnnd25b0iQJFvHnx3KORXkZrKN4gZgknVi3ouBvLg7i0BFhV4xaAQR7JIEYDgUiWSt5RVSqCu9UFGy+je/mU8kGsOUQG3VkXWPVZLd2K0yC+VlzWg82fFG6rjC13RHic6qVtC3zMiawlJnfnNnmuNnf9a725wA7VVNxK4iJbUywy1VJ1eMhje/+x9TG7WPtJ7yQPpImrFjK27KtsJHDn/HGSp/4bpfG7IAzf5431mFiiNecdME9J7CBgwXWnGX09cga36Ehtjn9yEeytR04wvqcqibvGWoCB489qMt2oAm2VJKsc+pZ3SYOn/p3zLtq/9j+Fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"Column1.1", "Column1.2", "Column1.3"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars=true]),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","",null,Replacer.ReplaceValue,{"Manager NAME"})
in
    #"Replaced Value"

Then use the PATH functions in DAX

From there you can set your filters for Direct or Indirect Reports, hierarchies etc.

[Anonymous]

Hello lbendlin,

Thank you! I have organized the data and added the path function. I am trying to use Matrix/slixer visual to see the org chart. The only problem is I am seeing the blank when I expand the tree. Is there a way to remove the blank from the visual? Attached is the copy of the Power Bi report and the source data.https://drive.google.com/drive/folders/1335DTWOxzHtXj8CplBzFffEwYyib0Pye?usp=sharing  

[lbendlin · Accepted solution]

The only problem is I am seeing the blank when I expand the tree. Is there a way to remove the blank from the visual?

Not unless you want to go to extremes with ISINSCOPE, or are willing to use the hierarchy slicer visuals that traverse the tree for you.  With your hierarchy size that will already incur a meaningful time penalty.

Note that you don't need to do that much of a cleanup.

let
    Source = Csv.Document(File.Contents("C:\Users\xxx\Downloads\Hierarchy data.csv"),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","",null,Replacer.ReplaceValue,{"LEADER_ID"})
in
    #"Replaced Value"

And SELECTEDVALUE makes little sense in calculated columns. Streamline your data.

level_2_NAME = var e = PATHITEM([path],2) return MAXX(FILTER(ADM,[EMPLID]=e),[NAME])