Using Decomposition Tree as Organization chart

[Suganthy]

Hi All, 

I am trying to use the decomposition tree  as an org chart, and below is how the data that am trying to use 

Unique Identifier	Level	Department	Ultimate parent	Parent	Level 1	Level 2	Level 3	level 4
a	L0	Marketing	a
B	L1	sales	a	a	B
C	L1	sales	a	a	C
D	L1	sales	a	a	D
e	L1	Marketing	a	a	e
f	L1	sales	a	a	f
g	L1	sales	a	a	g
h	L1	Marketing	a	a	h
i	L1	sales	a	a	i
j	L1	sales	a	a	j
k	L1	Marketing	a	a	k
l	L1	sales	a	a	l
m	L2	sales	a	e	e	m
n	L2	sales	a	e	e	n
o	L2	Marketing	a	e	e	o
p	L2	Marketing	a	e	e	p
q	L2	Marketing	a	e	e	q
r	L2	Marketing	a	e	e	r
s	L2	sales	a	e	e	s
t	L2	Marketing	a	e	e	t
u	L2	Marketing	a	e	e	u
v	L3	sales	a	e	e	v
w	L3	sales	a	e	e	m	w
x	L3	Marketing	a	e	e	m	x
y	L3	Marketing	a	e	e	m	y
z	L4	sales	a	e	e	m	w	z

When I use this I get the below view, and I could see blanks as they will not be existing in other levels but we need that to have in the unique identifier column so that when we filter the name we can see which level the name is in.

Is there a way we can end the node or remove the blanks if it is not present in other levels? see picture below 

Any help would be greatly appreciated.

Thanks,

Suganthy

[die77777777]

I solved this issue. You need to make a hierarch first in your table. lets say it goes: L0_ID,  L1_ID, L2_ID, L3_ID, L4_ID.

Next drag that into Explain By for Decomposition Tree Visual. 

Now use the code that Curbal Suggests for Matrix in this video:

Remove_Blanks = Switch(  True(), 
AND(ISINSCOPE(Hierarchy_Tbl[L0_ID), ISBLANK(VALUES(Hierarchy_Tbl[L0_ID]))) ||
AND(ISINSCOPE(Hierarchy_Tbl[L0_ID), ISBLANK(VALUES(Hierarchy_Tbl[L0_ID]))) ||
AND(ISINSCOPE(Hierarchy_Tbl[L0_ID), ISBLANK(VALUES(Hierarchy_Tbl[L0_ID]))) ||
AND(ISINSCOPE(Hierarchy_Tbl[L0_ID), ISBLANK(VALUES(Hierarchy_Tbl[L0_ID]))),
BLANK(),
COUNTROWS(Hierarchy_Tbl)-1  )

and use this Remove_Blanks as the Analyze field in the Decomposition Tree Visual.

[RealSir]

In your table create a column 'IsLeaf':

IsLeaf = 1*(ISBLANK(LOOKUPVALUE([Parent],[Parent],[Name])))

Leaf can't be a parrent, so it checks if given member exist on parent list, if it doesn't the result of lookup is blank. Add that to your visual as a filer and set it to be eaqual 1.

[Guido_Beulen]

What exactly would that look like using ISINSCOPE() combined with the PATH function? I can't seem to get this to work... 

[lbendlin]

No, you cannot suppress the display of the blanks. Decomposition trees do not support ragged hierarchies.

[Suganthy]

Hi,

Thanks for the reply.  I have tried using the org charts in the marketplace instead of the decomposition tree but the filters are not working. for eg: if I have to filter a person in level 9 I should see the nodes and flow which is not happening. Do you have any other suggestions?

[lbendlin · Accepted solution]

Use the DAX PATH functions, and ISINSCOPE() to suppress blanks.