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.