Forum Discussion
Direct and Indirect Report
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
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])
8 Replies
please check the link, it asks for credentials
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.