topic Table variable cannot be used in current context because a base is expected in DAX Commands and Tips

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

Anonymous — Wed, 22 Mar 2023 10:13:11 GMT

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

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

tamerj1 — Wed, 22 Mar 2023 11:29:10 GMT

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

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

Anonymous — Wed, 22 Mar 2023 12:34:27 GMT

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.

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

tamerj1 — Wed, 22 Mar 2023 12:54:27 GMT

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