Fetch Value of a column from 2nd table depending on condition from 1st table

[Anonymous]

Hi All,

I want to fetch the value of Date from TAB2 depending on the *Condition(listed below) from the TAB1.

TAB1
Company	Key field	Invoice number	Doc number
ABCX	15	123456	123456
ABCX	1	123456	1245987

TAB2
Company	Invoice number	Date
ABCX	123456	01-01-2000 10:00
ABCX	123456	02-01-2000 10:00

The condition is fetch the value of the date field from TAB2 for the same invoice number present in TAB1 only if the Key field value is "15" for the same invoice number in TAB1

This is what i have written but it says memory error. Any idea where I am going wrong?

Pay Date = if('TAB1'[Invoice Number] == 'TAB1'[Doc Number],if('TAB1'[key field] = "15",LOOKUPVALUE('TAB2'[Date],'TAB2'[Invoice Number],'TAB1'[Doc Number])))

I have created a new column and have applied the query to the same column

Thanks in Advance.

@amitchandak 

[v-yetao1-msft]

Hi @Anonymous 

If possible, please provide your pbix file(remove sensitive info) so that we can create the results you need based on your model .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Samarth_18]

Hi @Anonymous ,

You could try to create a column as below:-

Pay Date = 
IF (
    'Tab1'[Invoice Number] = 'Tab1'[Doc Number]
        && 'Tab1'[key field] = 15,
    CALCULATE (
        MAX ( 'Tab2'[Date] ),
        FILTER ( Tab2, 'TAB2'[Invoice Number] = 'Tab1'[Doc number] )
    )
)

Thanks,

Samarth

[Anonymous]

Hi Samarth,

I tried but I am getting Out of Memory error 

[tamerj1]

Hi @Anonymous 

Is there any relationship between the two tables? Can you create/modify relationships? Which of the dates would like to retrieve? For example the first date or the last date?

[Anonymous]

Yes there is relationship maintained between the tables.

I typed the second date accidentally, I just want the date value from tab 2 with the conditions mentioned above. 

[tamerj1]

@Anonymous 
Which columns are involved in the relationship?

[Anonymous]

I am getting this error even if i try doing it for just one entry 

[Anonymous]

@tamerj1 

[ValtteriN]

Hi,

Try something like this:

Date tab2 = IF('Table (8)'[Key field]="15",CALCULATE(MAX('Table (9)'[Date]),'Table (9)'[Invoice number]=EARLIER('Table (8)'[Invoice number])),BLANK())

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Anonymous]

I tried the solution, but I am getting memory error.

[ValtteriN]

In that case the problem is with your model optimization. Try creating a measure. E.g.

Date tab2_m = IF(max('Table (8)'[Key field])="15",CALCULATE(MAX('Table (9)'[Date]),'Table (9)'[Invoice number]=max('Table (8)'[Invoice number])),BLANK())

[Anonymous]

Hi, 

I am getting an error "unexpected parameter BLANK() when I type the measure. I have used the same code that you have provided