Forum Discussion
decarsul
Helper V
Helper VReturning value from inactive related table
Good day all, Today i'm trying to eventually create a measure to show a sum of a value in a bar or line chart, where the value is only show once, based on the max of phase. To do this, i need da...
decarsul
Here is the updated sample file https://we.tl/t-sttvJNdSEVAmount Measure = VAR T1 = SUMMARIZE ( 'Table 1', 'Table 1'[ID], 'Table 1'[Phase] ) VAR T2 = ADDCOLUMNS ( T1, "@MaxPhase", CALCULATE ( MAX ( 'Table 1'[Phase] ), ALLEXCEPT ( 'Table 1','Table 1'[ID] ) ), "@Amount", CALCULATE ( SUM ( 'Table 2'[Amount] ), USERELATIONSHIP ( 'Table 2'[ID], 'Table 1'[ID] ), CROSSFILTER ( 'Table 2'[ID], 'Table 1'[ID], BOTH ) ) ) VAR T3 = FILTER ( T2, [Phase] = [@MaxPhase] ) RETURN SUMX ( T3, [@Amount] )
tamerj1
Community Champion
Community Championdecarsul
Here you go https://we.tl/t-iaJTiS2ZNa
Amount Measure =
CALCULATE (
SUM ( 'Table 2'[Amount] ),
USERELATIONSHIP ( 'Table 2'[ID], 'Table 1'[ID] ),
CROSSFILTER ( 'Table 2'[ID], 'Table 1'[ID], BOTH )
)
decarsulHelper V
Helper VAlright, that seems to work.
Wasn't aware off the crossfilter function.
Time for validation!
- tamerj1
decarsul
Not sure about the result of the aggregation. It depends on your business logic but shall not an issue to amend.- decarsul
so i just validated. It doesn't add up.
I'm missing the MAX filter on the phase level.
Right now, its counting the amount per Phase, but i only want it to count based on MAX of the Phase.
Simply adding an MAX filter, doesn't work.
So how am i introducing the MAX for phase nr?