Table variable cannot be used in current context because a base is expected

[Anonymous]

Just like to test the function "LOOKUPVALUE".

I have two tables "Sales" and "Promo" where I like to add to the table "Sales" a column "Campaign" from the table "Promo" based on Sales[Product].

Dont understand why i get the error "Table variable cannot be used in current context because a base is expected" at "LOOKUPVALUE(PROMO".

How to solve?

Define
var Sales = DATATABLE("Customer", STRING,
                      "Product", STRING,
                      "Date", STRING,
                      "Quantity", INTEGER,
                      {
                       {"Marco","Mouse", "1/20/2017",2},
                       {"Marco","Tablet", "2/16/2017",1},
                       {"Alberto","Mouse", "1/30/1017",1},
                       {"Alberto","Tablet", "1/30/2017",1},
                       {"Alberto","Watch", "2/23/2017",1}
                      }
                     )
var Promo = DATATABLE("Month", INTEGER,
                      "Product", STRING,
                      "Campaign", STRING,
                      "Media", STRING,
                      {
                       {1,"Mouse","Bundle","Radio"},
                       {1,"Tablet","Bundle","Banner"},
                       {1,"Watch","Two-for-one","Newsletter"},
                       {2,"Mouse","Sale","Magazine"},
                       {2,"Watch","Sale","Newsletter"}
                      }
                     ) 
                     

var testTable = ADDCOLUMNS (
                            Sales,
                            "Campaign",
                            LOOKUPVALUE(
                                        Promo[Campaign],
                                        Promo[Product], Sales[Product]
                                       ) 
                           )                 

EVALUATE testTable

[tamerj1]

Hi @Anonymous 
In this context you need to use DEFINE TABLE rather than DEFINE VAR

[Anonymous]

Hi,

Thanks for your quick response.

Do you mean for each DEFINE VAR or only DEFINE TABLE SALES and DEFINE TABLE PROMO ?

In this case I got the message "A table of multiple values was supplied where a single value was expected.

When I define Table testTable as DEFINE TABLE testTable I got the same message.

[tamerj1 · Accepted solution]

@Anonymous 
Yes correct. And also the error is expected as the LOOKUPVALUE function in this case is returning multiple values not a single value. Please try

DEFINE
    TABLE Sales =
        DATATABLE (
            "Customer", STRING,
            "Product", STRING,
            "Date", STRING,
            "Quantity", INTEGER,
            {
                { "Marco", "Mouse", "1/20/2017", 2 },
                { "Marco", "Tablet", "2/16/2017", 1 },
                { "Alberto", "Mouse", "1/30/1017", 1 },
                { "Alberto", "Tablet", "1/30/2017", 1 },
                { "Alberto", "Watch", "2/23/2017", 1 }
            }
        )
    TABLE Promo =
        DATATABLE (
            "Month", INTEGER,
            "Product", STRING,
            "Campaign", STRING,
            "Media", STRING,
            {
                { 1, "Mouse", "Bundle", "Radio" },
                { 1, "Tablet", "Bundle", "Banner" },
                { 1, "Watch", "Two-for-one", "Newsletter" },
                { 2, "Mouse", "Sale", "Magazine" },
                { 2, "Watch", "Sale", "Newsletter" }
            }
        )
    VAR testTable =
        ADDCOLUMNS (
            Sales,
            "Campaign",
                MAXX (
                    FILTER (
                        Promo,
                        Promo[Product] = Sales[Product]
                            && Promo[Month] = MONTH ( DATEVALUE ( Sales[Date] ) )
                    ),
                    Promo[Campaign]
                )
        )

EVALUATE
testTable