Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
erhan_79
Post Prodigy
Post Prodigy

Lookup 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 

 

Capture1.JPG

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@erhan_79 

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])

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

9 REPLIES 9
visakhmurukes
Frequent Visitor

SUMMARIZE(
TableA,
TableA[OrderNo],
"LastPrice",LASTNONBLANKVALUE(TableA[DocumentDate],SUM(TableA[NetPrice]))
)

@visakhmurukes  Interesting differences in the produced  queries

 

lbendlin_0-1595782730896.png

versus

lbendlin_1-1595782835054.png

Your version has many more row scans but still comes out better as it avoids the CallbackDataID issue in my version.

thanks for info @lbendlin but this explaning so technical 🙂 i could not understand sorry , i am not responsible for IT 🙂 , so do you advice me anything else ?

Use whatever solution you are comfortable with .  My comment wasn't directed at you but at the person who (in my opinion) gave you the best solution.

thank you very much dear @lbendlin , for your helps and supports , nice to meet you , i will add you to my friends 

ryan_mayu
Super User
Super User

@erhan_79 

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])

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Here is a version that also gets the Totals right.

 

lbendlin_0-1595732288127.png

 

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

Here would be a simple measure that does that.

 

lbendlin_0-1595722339615.png

Note that the totals are wrong. Is that important for you?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.