Forum Discussion
Solved
Create Calculated Column using column value as variable in where clause
I want to create a calculated column in one table (call it TableA) that Calculates a value from another table (TableB) based on column value in TableA
ProdMax = CALCULATE(MAX(TABLEB[Value]) WHERE TABLEA[PRODUCT] = TABLEB[PRODUCT]
Try this:
ProdMax = VAR _Prod = TABLEA[PRODUCT] RETURN CALCULATE ( MAX ( TABLEB[Value] ), TABLEB[PRODUCT] = _Prod )Other variations:
ProdMax = CALCULATE ( MAX ( TABLEB[Value] ), TABLEB[PRODUCT] = EARLIER ( TABLEA[PRODUCT] ) )ProdMax = MAXX ( FILTER ( TABLEB, TABLEB[PRODUCT] = TABLEA[PRODUCT] ), TABLEB[Value] )
5 Replies
Try this:
ProdMax = VAR _Prod = TABLEA[PRODUCT] RETURN CALCULATE ( MAX ( TABLEB[Value] ), TABLEB[PRODUCT] = _Prod )Other variations:
ProdMax = CALCULATE ( MAX ( TABLEB[Value] ), TABLEB[PRODUCT] = EARLIER ( TABLEA[PRODUCT] ) )ProdMax = MAXX ( FILTER ( TABLEB, TABLEB[PRODUCT] = TABLEA[PRODUCT] ), TABLEB[Value] )I tried your 2nd suggestion and it worked. Thank YOU!!
You need to create a relationship between the two tables. In my response you can see how it is achieved. AlexisOlson measures work great but you need to get the relationship down for it all to be tied in together.
Why does STDEV.P not workin place of MAX
Prodstd =CALCULATE (STDEV.P ( Query1_TSP[Value] ),Query1_TSP[Att_BatchDesc] = EARLIER ( SPECS_BATCHDESC_ATTRIBUTE[Att_BatchDesc] ))
