Inconsistent measure results

[pwa42]

Situation: I was asked to create waterfall chart with starting and ending totals and dynamic structure of effects (breakdowns) - it should show different effects depending on position in organization (level). I created table with breakouts and since there would be just M:N relationships I created none and use a measure that would filter the correct set of effects.

Parent define for which node in org chart is the set of effects related. 
Measure for filtering is wollowing:

DF Simple Ebitda breakout = 

var selStructure = [Selected Hierarchy Code]
var result = 
    if(SELECTEDVALUE(T_Set_SimpleEbitda_effect[Parent]) = selStructure
        , 1
        , 0)

return result

For level 1 and 2 it behaves as intended. If marks exactly the set of effects I need:

But for level 3 the results are strange. It shows just one of the set (and from the middle of the set):

Strange is that when I add Parent column to vizual then it shows what I need. 

Would you have any clues where the problem might be? Thany you for any help!

[pwa42 · Accepted solution]

Problem solved!

The main cause was that sets of effects for level 3 were not disjoint in some cases (Effect name Price was defined for more Parents). So when I used SELECTEDVALUE(T_Set_SimpleEbitda_effect[Parent]) to compare with single selected Code in hierarchy, it returned blank().

When I used VALUES instead of SELECTEDVALUE roblem was solved! 

DF Simple Ebitda breakout = 

var selStructure = [Selected Hierarchy Code]

var result = 
    CALCULATE(
        if(selStructure in VALUES(T_Set_SimpleEbitda_effect[Parent])
            , 1
            , 0)
    )
return result

 @lbendlin could not see this, the data sample was too small.

Thanks for hints!

[lbendlin]

What's the definition of 

[Selected Hierarchy Code]

?

[pwa42]

it selects code from hierarchy company structure

Selected Hierarchy Code = 
VAR lvl = [Selected Hierarchy Level]

return
    SWITCH (
        lvl,
        0, SELECTEDVALUE('Structure'[L0_name]), // in this case it is better holding abrev. 
        1, SELECTEDVALUE('Structure'[L1_code]),
        2, SELECTEDVALUE('Structure'[L2_code]),
        3, SELECTEDVALUE('Structure'[L3_code]),
        4, SELECTEDVALUE('Structure'[L4_code]),
        5, SELECTEDVALUE('Structure'[L5_code]),
        6, SELECTEDVALUE('Structure'[L6_code]),
        "No filter"
    )

definition of [Selected Hierarchy Level] is

Selected Hierarchy Level = 
//VAR IsL0sel = ISFILTERED( 'Structure'[Scope Name] ) // replaced L0 - it is related in a fact
VAR IsL0sel = ISFILTERED( 'Dim Scope Listed'[Scope Name] ) // replaced L0 - it is related in a fact
VAR IsL1sel = ISFILTERED( 'Structure'[L1_name] )
VAR IsL2sel = ISFILTERED( 'Structure'[L2_name] )
VAR IsL3sel = ISFILTERED( 'Structure'[L3_name] )
VAR IsL4sel = ISFILTERED( 'Structure'[L4_name] )
VAR IsL5sel = ISFILTERED( 'Structure'[L5_name] )
VAR IsL6sel = ISFILTERED( 'Structure'[L6_name] )

return
    SWITCH (
        TRUE (),
        IsL6sel, 6,
        IsL5sel, 5,
        IsL4sel, 4,
        IsL3sel, 3,
        IsL2sel, 2,
        IsL1sel, 1,
        IsL0sel, 0,
        -1
    )

it is based on Slicers used in page. The structure has 7 levels from holding entity (0) to final leaf company (4 - 6).

Results are OK when user is in hierarchy level 0 - 3.

[lbendlin]

Is your hierarchy completely filled or ragged ?

[pwa42]

it is ragged

[lbendlin]

Then I am not surprised to see inconsistent results.  SQLBI have a couple of articles on that, and RADACAD too. This is a rather complex topic.

[pwa42]

It seems that ragged hierarchy is a fake clue. I altered the hierarchy so it is comletely filled and the problem remains.

Any other ideas?

[pwa42]

Well I am quite surprised, I would not expect to see this result. Even when the structure is ragged, i have 1 scalar result in measure [Selected Hierarchy Code]. Then I would expect consistent result when I filter by this result since there is no relation that would change the results. 
Could you please link some of those articles you mentioned?