Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

LOOKUPVALUE with multiple matches in Search_ColumnName

Hi everyone,

 

I'm on a Power BI project needing me to use the LOOKUPVALUE function (I guess)

For example I have:

 

TABLE 1:

Code_t1             Value         Date

A100             100            09/2019

A200             200            09/2019

A100             110            10/2019

A200             210            10/2019

...

 

TABLE 2:

Code_t2            resultColumn

A100             

A100

A100

A100

 

The goal of the project is to convert an old Excel file used as reporting into a power BI Project.

datas are collected every months on last day.

I have the following formula in resultColumn:

 

ResultColumn = LOOKUPVALUE(Table1[Value];Table1[Code_t1];Table2[Code_t2])
 

It's working when each Code are unique in Table1, but not with identical values (when we have 2 or more months),

 
Something else that can help: the couple Code_t1/Date is always Unique !
 
so how can i do to collect all Values even if the code is not unique, maybe using the date to help ? 
Lookupvalues

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi

    you can try the following

     

    Add a calculated column on table2
    Latest Result =
    VAR Current_Code = Table2[Code_t2]
    return CALCULATE(sum(Table1[value]), FILTER(Table1, Table1[Code_t1] = Current_Code))

     

    Let me know if it works

    Tomas

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello Tomas,

       

      Thanks you for your answer,

       

      Your function is correct and give me a value wich is the SUM of Value month 1 +Value month 2,

      I think I was not working in the right way using column ^^

      Is it possible in a measure to filter on date and choose what value to display depending on the date ?

       

      I tried something like this:

      measureResult = LOOKUPVALUE(Table1[Value];Table1[Code DMS];DISTINCT(Table2[Code_t2]);Table1[Date];MAXA(Table1[Date]))
       
      Now I can Filter on date and choose what value to display.
      The problem is that I have to put a filter on Code_t2 too but it's not what I need, I want to be able to see all Values for each Code_t2 in the same view.