Forum Discussion
Solved
Inconsistent measure results
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...
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 resultlbendlin could not see this, the data sample was too small.
Thanks for hints!
What's the definition of
[Selected Hierarchy Code]?
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.