Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors