Forum Discussion

JimT99's avatar
JimT99
Icon for Helper I rankHelper I
2 years ago
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[Valu...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    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]
)

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

I replicated your data. Solution is as follows. Create a relationship between table A and B based on the product field. Then create a measure : 

MAX VALUE = CALCULATE(MAX('Table B'[Value])). After create a table with the MAX Value measure, and product field from TABLE A.