Forum Discussion

RachelS0815's avatar
RachelS0815
Regular Visitor
4 years ago
Solved

Lookup Values on Multiple Criteria Returns Different Values Under Same Condition

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

 

 

  • RachelS0815's avatar
    RachelS0815
    4 years ago

    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

5 Replies

  • CarlesMB's avatar
    CarlesMB
    Regular Visitor
    4 years ago

    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's avatar
      RachelS0815
      Regular Visitor
      4 years ago

      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's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        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