Forum Discussion
Post ProdigyLookup VAlue for repeating values
Hi there ;
i need your help for below issue ;
i have two table , Table A and Table as below , for both table i have order no , i would like to make lookup value for net price to Table B , but there will be rule like that ;
the net price which will transfer to Table B , will be the net price of the last date's net price always
please try to create a column in table B
NET PRICE = VAR _MAXDATE = MAXX(FILTER('Table A','Table A'[order no]='Table B'[order no]),'Table A'[document date]) RETURN MAXX(FILTER('Table A','Table A'[order no]='Table B'[order no] && 'Table A'[document date]=_MAXDATE),'Table A'[net price])
9 Replies
- lbendlin
Super User
Here would be a simple measure that does that.
Note that the totals are wrong. Is that important for you?
- Ashish_Mathur
ryan_mayuplease try to create a column in table B
NET PRICE = VAR _MAXDATE = MAXX(FILTER('Table A','Table A'[order no]='Table B'[order no]),'Table A'[document date]) RETURN MAXX(FILTER('Table A','Table A'[order no]='Table B'[order no] && 'Table A'[document date]=_MAXDATE),'Table A'[net price])- lbendlin
Here is a version that also gets the Totals right.
SUMMARIZE( TableA, TableA[OrderNo], "LastPrice",LASTNONBLANKVALUE(TableA[DocumentDate],SUM(TableA[NetPrice])) )- lbendlin
visakhmurukes Interesting differences in the produced queries
versus
Your version has many more row scans but still comes out better as it avoids the CallbackDataID issue in my version.
