Forum Discussion
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 (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!
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!
8 Replies
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.
Is your hierarchy completely filled or ragged ?
