Lookup Values on Multiple Criteria Returns Different Values Under Same Condition

[RachelS0815]

Hello,

I am new in DAX, and I try to look up a value under multiple conditions. However, it returns the correct value in some columns but not all. But the criteria is the same. I try to circle back to the lookup file to see where this number comes from but it shows that it cannot find. I am so confused what I did wrong. I attached partial of the report and the lookup table. 

Many Thanks,

QOH VALUES = LOOKUPVALUE('QOH SUM'[Month Cost To Date],'QOH SUM'[ITEM NO_],'Inventory DIO'[ITEM_NO],'QOH SUM'[LOCATION CODE],'Inventory DIO'[Location Code],'QOH SUM'[MON_IND],'Inventory DIO'[MON_IND])

[CarlesMB]

I guess that you need to sum all the values of the same Item_no, Location Code and Mon_Ind.
I think that you should have to try with:
Calculate(
Sumx("Table_0", "Table_0.Month cost to date"),
Filter (All ( table_0), Table_0.Item no = Table_1.Item no &&  Table_0.Mon ind = Table_1.Mon ind && Table_0.Location Code = Table_1.Location Code))

[RachelS0815]

Thanks for the reply. However, in my lookup file, it is only 1 value under the same criteria, ie the value is 119768.97 when item is 270553, location is BC Surrey and Month is 2020-09-01, so I don't need to do sum but just lookup value. 

[v-yanjiang-msft]

Hi @RachelS0815 ,

I create a sample and can't reproduce your problem. Is QOH VALUES a calculated column? Did the two tables have relationship, in which column did they relate. What's the difference between rows got the correct value and rows got the wrong value.

Best Regards,
Community Support Team _ kalyj

[RachelS0815 · Accepted solution]

Hi Kalyj,

Thanks for you reply. I finally found out the issue yesterday that Power BI sum the values when I added. Once I removed the sum, it is fixed. 

Regards,

Rachel

[v-yanjiang-msft]

Hi @RachelS0815 ,

Glad your problem is solved. If you don't have other problems, would you please accept your answer as the solution, then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj