topic Re: Issue with Lookupvalue Formula in DAX Commands and Tips

Issue with Lookupvalue Formula

Chandrashekar — Thu, 14 Apr 2022 05:18:44 GMT

Hello,

am trying to get value from Table1 to Table2 where I used below lookupvalue formula but am getting Not Found as value is available in Table1.

I tried two formula(as below) but getting same result. However same works for small data(around 10 rows) but not for huge data which have more than 10K.

Kindly advise is there any issue with the below formula and cannot share pbix file as it is confidential.

Formula1:

In Task = LOOKUPVALUE(Task[Number],Task[Number],'Task SLA'[Number],"Not Found")

In Task1 = CALCULATE(firstnonblank(Task[Number],TRUE()),FILTER('Task','Task SLA'[Number]=[Number]))

Re: Issue with Lookupvalue Formula

tamerj1 — Thu, 14 Apr 2022 05:28:19 GMT

Hi Chandrashekar
Can you please provide sample data and advise the relationship between the two tables?

Re: Issue with Lookupvalue Formula

Chandrashekar — Thu, 14 Apr 2022 05:51:29 GMT

Hello,

I have established relationship on date field. Table1 have unique value(Number) however Table2 have duplicate value(Number).

Unfortunately I cannot share file as it confidential but I copied few records(10 rows) in another table and it is working fine but for huge data(12K) it is not working.

Regards,

Chandrashekar B

Re: Issue with Lookupvalue Formula

Chandrashekar — Thu, 14 Apr 2022 05:55:39 GMT

Hello,

I have established relationship on date field. Table1 have unique value(Number) however Table2 have duplicate value(Number).

Unfortunately I cannot share file as it confidential but I copied few records(10 rows) in another table and it is working fine but for huge data(12K) it is not working.

Regards,

Chandrashekar B

Re: Issue with Lookupvalue Formula

tamerj1 — Thu, 14 Apr 2022 05:57:38 GMT

Chandrashekar
Yes because in big data you will have duplicates which is the case when LOOKUPVALUE returns blank. Same problem occurres when the data contains blanks, as blanks are treated as duplicates. What kind of result did you receive when applying to big data?

Re: Issue with Lookupvalue Formula

Chandrashekar — Thu, 14 Apr 2022 06:11:16 GMT

Hello,

If am understand you correctly. am getting Not Found as I have used it in formula.

Just now I copied data which am getting Not Found in separable table(400 records) and used lookupvalues formula where formula is working perfectly.

Is there anyway how to get desired result.

Formula used:

In Task = LOOKUPVALUE(Task[Number],Task[Number],'Task SLA'[Number],"Not Found")

Regards,

Chandrashekar B

Re: Issue with Lookupvalue Formula

Chandrashekar — Sun, 17 Apr 2022 08:25:12 GMT

Hello,

I did work around(Copied Number Only) to separate table. I used lookupvalue and worked fine.

Kindly explain why same formula is not working in Task SLA(which have duplicate number.

Regards,

Chandrashekar B

Re: Issue with Lookupvalue Formula

tamerj1 — Sun, 17 Apr 2022 08:50:42 GMT

Chandrashekar

Please share some sample data. Make sure that the sample also reflects the problem