Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Add column based on lookup in same table

Hi,

 

I'm trying to understand how I can create a column with a Lookup in the same table using a DAX expression

 

In excel it would look like this, I'm looking to match the records of the last month date with alookup in the 'Date' column

 

 

In DAX I'm trying this 

last month value = LOOKUPVALUE(table[Records],table[Date],table[Last Month Date].[Date])
 
But I get the error "A table of multiple values was supplied where a single value was expected."
 
what am I doing wrong?
 
many thanks for your help

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

     

    Create a Calculated Column

     

    Column = LOOKUPVALUE(Table5[Records],Table5[Date],Table5[Last Month Date])

     

     

    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 

    VAR SearchValue = <Search_Value>
RETURN
    CALCULATE (
        SELECTEDVALUE ( <Result_ColumnName>, <Alternate_Result> ),
        FILTER (
            ALLNOBLANKROW ( <Search_ColumnName> ),
            <Search_ColumnName> == SearchValue     -- The == operator distinguishes between blank and 0/empty string
        ),
        ALL ( <table_of_Result_ColumnName> )       -- If Result_ColumnName is t, this is ALL ( t )
    )

     

    Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!!

     

    Regards,
    Pranit

     

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , for a column you can try like

    sumx(filter(table,[date] = earlier([last month Date])),[record])
    sumx(filter(table,[date] = earlier([last month Date])),earlier([record]))

     

    or

     

    sumx(filter(table,month([date]) = month(earlier([last month Date]))),[record])
    sumx(filter(table,month([date]) = month(earlier([last month Date]))),earlier([record]))

     

     

    Can you share sample data and sample output in table format?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      I tried
      recors last month = sumx(filter(table),table[Date] = EARLIER(table[Last Month Date].[ Date]),table[records]
      )
      but get this message "few arguments were passed to the FILTER function. The minimum argument count for the function is 2."