topic Re: Lookup Values on Multiple Criteria Returns Different Values Under Same Condition in DAX Commands and Tips

Lookup Values on Multiple Criteria Returns Different Values Under Same Condition

RachelS0815 — Fri, 03 Jun 2022 14:17:47 GMT

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

Re: Lookup Values on Multiple Criteria Returns Different Values Under Same Condition

CarlesMB — Fri, 03 Jun 2022 15:05:57 GMT

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

Re: Lookup Values on Multiple Criteria Returns Different Values Under Same Condition

RachelS0815 — Fri, 03 Jun 2022 15:11:24 GMT

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.

Re: Lookup Values on Multiple Criteria Returns Different Values Under Same Condition

v-yanjiang-msft — Thu, 09 Jun 2022 08:12:31 GMT

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

Re: Lookup Values on Multiple Criteria Returns Different Values Under Same Condition

RachelS0815 — Thu, 09 Jun 2022 13:07:01 GMT

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

Re: Lookup Values on Multiple Criteria Returns Different Values Under Same Condition

v-yanjiang-msft — Fri, 10 Jun 2022 01:06:49 GMT

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