Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hierachrchy with additional values next to it.

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
 
 
 

 

DAX Hierarchy

1 Reply

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    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.