Forum Discussion
Direct and Indirect Report
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])
please check the link, it asks for credentials
Hello.. Sorry about that. Shared a new link. Thank you!
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.
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
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])