Forum Discussion

decarsul's avatar
decarsul
Icon for Helper V rankHelper V
4 years ago
Solved

Returning 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...
Need Help
Show and Tell
Tips and Tricks
  • tamerj1's avatar
    tamerj1
    4 years ago

    decarsul 
    Here is the updated sample file https://we.tl/t-sttvJNdSEV

    Amount 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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

decarsul 
Here is the updated sample file https://we.tl/t-sttvJNdSEV

Amount 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] )
  • decarsul's avatar
    decarsul
    Icon for Helper V rankHelper V
    4 years ago

    Seems to work again. Time to validate!

     

    Validated, works as intended. 

    Thanks for the help!