LOOKUPVALUE with multiple matches in Search_ColumnName

[Anonymous]

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 ? 

[Anonymous]

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]

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.