last address for each record

[edvardoss]

Hello.
I have a dataset (one table) containing the store's name, the date of the invoice, and the address of the store.
The problem is that the address of the store may vary due to extra spaces, dots, introducing cuts or relocating to a new store.
Purpose - to assign each entry a calculated field, in which the common address will be displayed in the last date of sale.
To do this, create an intermediate column for the latest date of sale

[FONT=Courier New]MaxDatePos:=CALCULATE(MAX('tab1'[InvoiceDate]);ALLEXCEPT('tab1';'tab1'[Name]))[/FONT]

Well, it works correctly.
but the next step is causing the error:
took this formula = LOOKUPVALUE(<result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]...)
and wrote

[FONT=Courier New]=LOOKUPVALUE('tab1'[Adress];'tab1'[Name];[Name];'tab1'[InvoiceDate];[MaxDatePos])[/FONT]

this formula produces an error.
ask for help.

 

[scottsen]

Can you include the error message please?

 

[edvardoss]

Can you include the error message please?

Calculation error in the column 'Table1' []: expected a single value, but assigned to a table or multiple values.

 

[Tianbas]

Cant really help but I have a follow up question -> How to solve the above issue without LOOKUPVALUE. At least I dont have this function in my old PowerPivot version. I can return a number or a date using CALCULATE (SUM/MAX/AVERAGE/.... and EARLIER to lookup a value in the same table but I could not find a solution to return a text like an adress. I tried FIRSTNONBLANK and some FILTER functions but could not get a working formula.

 

[scottsen]

So... you are passing [name], and I get the feeling it is grumpy about that. While logically that should be just 1 name (since we are iterating 1 row at a time in this calculated column), I get the feeling it is not working like we expect there.

Can you try passing CALCULATE(MAX(

)) -- the calculate is indeed required, otherwise it would try and MAX the entire table, the CALCULATE will constrain to the current row.

Tianbas, FIRSTNONBLANK was definitely the right path there. Just pass a 1 as the 2nd param to FIRSTNONBLANK.

 

[Tianbas]

Thanks Scott, to pass a 1 was the missing part

Working Formula for those that dont have a LOOKUPVALUE formula
=calculate(FIRSTNONBLANK(Tab1[Adress],1),Filter(Tab1,Tab1[InvoiceDate]=earlier(Tab1[MaxDatePos]) && Tab1[Name]=earlier(Tab1[Name])))

 

[edvardoss]

Thanks for formula, its work!

On another forum I suggested something similar.

= CALCULATE(   LASTNONBLANK(Table1[Address]; TRUE());    FILTER(     ALLEXCEPT(Table1; Table1[Name]);      Table1[InvoiceDate]        = CALCULATE(           MAX(Table1[InvoiceDate]);            ALLEXCEPT(Table1;Table1[Name])         )   ) )

Which option do you think most optimal performance?

 

[Tianbas]

using FILTER has an impact on performance but for majority of data models that is not an issue. If your model is so big that you need to improve performance wherever possible you should still try to get the LOOKUPVALUE formula running. I think this should have the best performance as this function is designed for that purpose.