Create conditional measure

[rgtanna]

Greetings, 

I'm trying to create a conditional measure that sums up all "stage 1" values only if "stage 3" has a value for the row item.

The data below is of sample source data, but when importing the data, I have unpivot the Stage 1 through to Stage 3 columns.

Item	Stage 1	Stage 2	Stage 3
1	100	100	100
2	100	100	100
3	100	100
4	100

What sort of measure is best to calculate this?

Thanks in advance!

[d_gosbell · Accepted solution]

It would be easier if we could see the data as it exists in the tabular table. But if I understand your issue correctly the following psuedo code should work

= SUMX( table1 ,
   IF( calculate(SUM(table1[Value]) , table1[Stage] = "Stage 3) > 0 ,
      IF( table1[Stage] = "Stage 1", table1[Value] )
   )
)

[v-jiascu-msft · Accepted solution]

Hi @rgtanna ,

Please also try this one.

Measure 2 =
VAR hasStage3Items =
    CALCULATETABLE ( VALUES ( Table1[Item] ), Table1[Stage] = "Stage 3" )
RETURN
    CALCULATE (
        SUM ( Table1[Value] ),
        FILTER ( Table1, Table1[Item] IN hasStage3Items && Table1[Stage] = "Stage 1" )
    )

Best Regards,

[d_gosbell · Accepted solution]

It would be easier if we could see the data as it exists in the tabular table. But if I understand your issue correctly the following psuedo code should work

= SUMX( table1 ,
   IF( calculate(SUM(table1[Value]) , table1[Stage] = "Stage 3) > 0 ,
      IF( table1[Stage] = "Stage 1", table1[Value] )
   )
)