Hierarchy level logic

Question

Hello,&nbsp;&nbsp;Let's say I have a data structure like this&nbsp;Level 1&nbsp; &nbsp; &nbsp; &nbsp; Level 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; StatusA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ApprovedA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ApprovedA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ApprovedB&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; B1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ReviewB&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ApprovedB&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;B3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ReviewC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ApprovedC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;C2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ApprovedC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; C3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Approved&nbsp;Only when all items in level 2 are approved, level 1 items are approved. So how do I write a measure to count how many level 1 items are approved?&nbsp;Thanks for the help in advance!

erd · Accepted Answer

glycine76&nbsp;,There are many ways... You can try this:FinalStatus = 
VAR t = 
    ADDCOLUMNS (
        DISTINCT ( Table[Level 1] ),
        "approved",
            VAR c_level = CALCULATE ( SELECTEDVALUE ( Table[Level 1] ) )
            VAR level1count = CALCULATE ( COUNT ( Table[Level 1] ), Table[Level 1] = c_level )
            VAR statusCount = CALCULATE ( COUNT ( Table[Status] ), Table[Status] = "Approved" )
            RETURN
                IF ( INT ( statusCount = level1count ) = 1, 1, BLANK () )
    )
RETURN
SUMX ( t, [approved] )&nbsp;

Forum Discussion

Hierarchy level logic

2 Replies

Related content

Matrix Visual Hierarchy Level Problem

Logic problem?

Measure logic for different hierarchy level

Dynamic Y range visual calculation in two level hierarchy

Multi level hierarchy in Gantt chart by microsoft corporation