Lookup a value on another row of the same table

[jesuisbenjamin]

Hello,

I have a table listing orders from cusomters. I want to find if a customer who ordered this year has also ordered for next year. I created a column concatenating YEAR&CUSTOMERID, and a NEXTYEAR&CUSTOMERID column. This way I hope to be able to lookup the amount of the order for that same customer next year.

I'm hoping to write a formula of the type 

IF([ YEARCUSTOMERID ] = [ @NEXTYEARCUSTOMERID ], ORDERAMOUT, 0 )

What DAX formula should I use?

[Greg_Deckler]

Can you provide a sample of your data. I can't picture it in my head that, for example:

Year      CustomerID

2016     12345

NextYear    CustomerID

2017           12345

That if you concantenate those that you can make a logical check if they are equal to one another.

201612345 <> 201712345

There are a couple of techniques I can think of but are dependent on your Customer ID, etc.

[jesuisbenjamin]

Thanks. In fact I should precise I'm trying to measure attrition.

Example (where table contains many customers):

Year  | Customer ID | Status      | Items | Amount | YearCustomerID | NextYearCustomerID | ItemsNextYear | ItemsLastYear

2015 | 123456789   | New         | 16      |  5000      | 2015123456789 | 2016123456789         | 0                      | 0

2015 | 123456789   | Returning | 18      |  5000      | 2015123456789 | 2016123456789         | 0                      | 16

2016 | 123456789   | Attrition    | 0        |  0            |2016123456789  | 2017123456789         | 0                      | 18

I'm not sure whether having a YearCustomerId or NextYearCustomerID or LastYearCustomerID concatenation is the way to go, but thats the only thing I could come up with in the absence of a formula / function that can return a value based on several conditions (same ID, next year).

Anyway this example should clearly lay out the problem.

[v-micsh-msft]

Hi jesuisbenjamin,

How about we take use of lookupvalue function here? This function could look up one or more reference column and return the result-column result.

For example, if we would like to find the next year Item, then we could take use of LOOKUPVALUE (Reference) function in a new column in the following way:

NextYearItem = lookupvalue(
              Table[Items], 
              Table[Customer ID], Value(Table[Customer ID]), 
              Table[Year], Table[Year]+1)

 If Filters not match, then the corresponding field would be empty. See an example:

If any further assistance needed, please feel free to post back.

Regards

[jesuisbenjamin]

Thanks. Does the VALUE(table[column]) stand for the [@value_on_this_row] in Excel the table syntax? I'm not sure how else PowerBI makes the difference between column references and cell references.

My syntax is: 

Purchase Next = LOOKUPVALUE(output[Purchase],output[CalendarYearPerson],VALUE(output[CalendarNextYearPerson]))

However. it does not take the value I pass in the search value and returns an error message: "A table of multiple values was supplied where a single value was expected."

[jesuisbenjamin]

I know for a fact that it's not possible and a quick test = CALCULATE(COUNT(output[CalendarYearPerson]),output[CalendarYearPerson]) reveals no duplicates.

[v-micsh-msft]

Hi jesuisbenjamin,

Apologize for the late response.

Would you please show me some data examples under your output table?

output[CalendarYearPerson],VALUE(output[CalendarNextYearPerson])

The value parameter should be value(output[CalendarYearPerson]), if there are no duplicates under this column, then a single value should be able to find the corresponding result column.

By the way, how the output[CalendarNextYearPerson]) calculated here? Have you tried to take use of the Year column?

Regards

[jesuisbenjamin]

I might have some duplicate for some reason. Let me try to find a unique identifier or forge one based on user ids and time indice to see if I can lookup.

[v-micsh-msft]

jesuisbenjamin,

Any update here?

If any further help needed, please feel free to psot back.

Regards

[jesuisbenjamin]

Thanks. Not yet. I'll get back to it soon and update.

[JosephineUa]

Hi, 

I have similar issue, I need to lookup value in a same table, I tried to write query on language M, as I use Power Query in Excel. I know how to write it with an addtitional query and merge it, but I don't know how to do it in same query, as my data has over million lines and if I add another query with million lines it takes a lot of time and PC resources to update it .

I can also use DAX in Power Pivot, but I have an error and don't know how to fix it. 

I have next data:

1st step: I need to look up user ID2 by colum user ID1 and show user mail

I tried =LOOKUPVALUE([User_ID2];[User_ID1];[user_email]), but it shows me an error "Function 'LOOKUPVALUE' does not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

I tried to fix it with FORMAT formula, but dont understand syntax FORMAT(<value>, <format_string>) and I still have an error, LOOKUPVALUE([User_ID2];[User_ID1]; Format ([user_email], text).

2nd step: combine user id2 with 1st step and show (result #1)

3rd step: combine all project names with same user id2 (result #2)

I know how to do 2nd and 3rd step in Power Query editor, but i suppose if I'll do 1st step in DAX this column will not be awailable in editor... So probably would need to do all steps in DAX

[v-micsh-msft]

Hi,

The error means in current table, of column output[CalendarYearPe​rson], there are several rows matches the current VALUE(output[CalendarNextYearPerson]).

We need to add another column and value pair to make the result column has only one unique value.

Regards