Hierachrchy with additional values next to it.

[Anonymous]

Hey all,

I am trying to create a status overview with an hierachy that should look the following (Used random values):

My Excel looks like this:

ID      Name      Value

1        a             1

1.1       aa          10

1.2      ab           11

2         b              3

2.1      ba            15

2.1.1   baa           20

3         c               25

Name:                 Value:

a                           1

   aa                      10

   ab                      11

b                           3

    ba                     15 

         baa               20

c                            25

I tried this using dax functions and building a hierarchy and then put it in a matrix and added the Value Colum.

However my hierarchy looks like this:

I already did plenty of research trying to solve this problem but could not find a solution.

I tried it with one of Curbals-Videos with the ISBLANK function by creating the measure:

Rem = SWITCH(TRUE();

AND(ISINSCOPE('Sheet1'[L2]);ISBLANK(VALUES(Sheet1[L2]))); BLANK();

AND(ISINSCOPE(Sheet1[L3]);ISBLANK(VALUES(Sheet1[L3]))); BLANK();

COUNTROWS(Sheet1))

... but this does only work if I do not add another value to the matrix.

Is there a way to solves this problem?

Thank you very much!

Philipp

[sturlaws]

Hi, @Anonymous,

have you looked at this:
https://www.daxpatterns.com/parent-child-hierarchies/

If you set up the hierarchy according to daxpatterns you can create this measure:

Measure value = 
SWITCH(true(),
    [BrowseDepth] > [MaxNodeDepth] , BLANK (),
    [BrowseDepth] = 1, CALCULATE(sum(Org[Value]),filter(org,ISBLANK(Org[Level 2]))),
    [BrowseDepth] = 2, CALCULATE(sum(Org[Value]),filter(org,ISBLANK(Org[Level 3]))),
    [BrowseDepth] = 3, CALCULATE(sum(Org[Value]),filter(org,Org[IsLeaf]))
)

where 

BrowseDepth = 
ISFILTERED ( org[Level 1] )
    + ISFILTERED ( org[Level 2] )
    + ISFILTERED ( org[Level 3] )

and 

MaxNodeDepth = MAX ( org[HierarchyDepth] )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.