topic Error when I want to create a measure with a temporary table in DAX Commands and Tips

Error when I want to create a measure with a temporary table

OlivierPag — Fri, 19 May 2023 06:19:58 GMT

Hello

I wants to create a measure based on a temporary table but despite all my effort I still have the same error
Table variable 'Rend' cannot be used in current context because a base table is expected.

The code is this one

Measure2 =

VAR Rend = ADDCOLUMNS(

SUMMARIZE(

control_result,

control_result[campaign]),

"comment",CALCULATE(DISTINCTCOUNTNOBLANK(control_result[controlresultid]),control_result[approver_comment]<10,control_result[performer_comment]<10),

"orby", LOOKUPVALUE(Campaign_Sort[Sort By],Campaign_Sort[Campaign],Rend[campaign]))

VAR numerateur = sumx(Rend,(Rend[orby]-AVERAGE(Rend[orby]))*(Rend[comment]-AVERAGE(Rend[comment])))

VAR Denominateur = sumx(Rend,(Rend[orby]-AVERAGE(Rend[orby]))*(Rend[orby]-AVERAGE(Rend[orby])))

RETURN

IF (DIVIDE(numerateur,Denominateur)<0,

"↓",

IF(DIVIDE(numerateur,Denominateur)>0,"↑","→"))

Re: Error when I want to create a measure with a temporary table

johnt75 — Fri, 19 May 2023 08:55:23 GMT

You can't refer to columns from a variable by using the TableName[ColumnName] syntax, you have to just use [ColumnName]. Try

Measure2 = VAR Rend = ADDCOLUMNS ( SUMMARIZE ( control_result, control_result[campaign] ), "comment", CALCULATE ( DISTINCTCOUNTNOBLANK ( control_result[controlresultid] ), control_result[approver_comment] < 10, control_result[performer_comment] < 10 ), "orby", LOOKUPVALUE ( Campaign_Sort[Sort By], Campaign_Sort[Campaign], Rend[campaign] ) ) VAR numerateur = SUMX ( Rend, ( [orby] - AVERAGEX ( Rend, [orby] ) ) * ( [comment] - AVERAGEX ( Rend, [comment] ) ) ) VAR Denominateur = SUMX ( Rend, ( [orby] - AVERAGEX ( Rend, [orby] ) ) * ( [orby] - AVERAGEX ( Rend, [orby] ) ) ) RETURN IF ( DIVIDE ( numerateur, Denominateur ) < 0, "↓", IF ( DIVIDE ( numerateur, Denominateur ) > 0, "↑", "→" ) )

Re: Error when I want to create a measure with a temporary table

OlivierPag — Fri, 26 May 2023 11:44:51 GMT

Thank you it works