cancel
Showing results for
Did you mean:
New Member

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

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 =
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,"↑","→"))
1 ACCEPTED SOLUTION
Super User

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 =
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, "↑", "→" )
)
``````
2 REPLIES 2
New Member

Thank you it works

Super User

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 =
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, "↑", "→" )
)
``````

Announcements

Power BI May 2023 Update

Find out more about the May 2023 update.

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors
Top Kudoed Authors