Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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].   D...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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
tamerj1's avatar
tamerj1
Community Champion
3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      tamerj1
      Community Champion
      3 years ago

      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