cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

1 ACCEPTED SOLUTION
Super User

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 5
Helper IV

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.

Super User

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

Frequent Visitor

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

I tried your 2nd suggestion and it worked.  Thank YOU!!

Helper IV

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.