topic Re: Fetch Value of a column from 2nd table depending on condition from 1st table in DAX Commands and Tips

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

Anonymous — Sat, 20 Aug 2022 09:22:51 GMT

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

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

Anonymous — Sat, 20 Aug 2022 09:23:26 GMT

tamerj1

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

ValtteriN — Sat, 20 Aug 2022 10:31:41 GMT

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/

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

Anonymous — Sat, 20 Aug 2022 10:39:54 GMT

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

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

Anonymous — Sat, 20 Aug 2022 10:46:48 GMT

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

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

ValtteriN — Sat, 20 Aug 2022 10:47:34 GMT

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

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

tamerj1 — Sat, 20 Aug 2022 10:48:13 GMT

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?

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

Samarth_18 — Sat, 20 Aug 2022 10:51:45 GMT

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

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

Anonymous — Sun, 21 Aug 2022 17:16:59 GMT

Hi,

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

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

Anonymous — Sun, 21 Aug 2022 17:19:28 GMT

Hi Samarth,

I tried but I am getting Out of Memory error

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

Anonymous — Sun, 21 Aug 2022 17:24:37 GMT

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.

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

tamerj1 — Sun, 21 Aug 2022 18:42:51 GMT

Anonymous
Which columns are involved in the relationship?

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

Anonymous — Thu, 01 Sep 2022 09:46:19 GMT

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.