Skip to main content
cancel
Showing results for 
Search instead 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

Reply
OlivierPag
Regular Visitor

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

View solution in original post

2 REPLIES 2
OlivierPag
Regular Visitor

Thank you it works

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.