Forum Discussion
Simple LOOKUPVALUE
I need help with a simple LOOKUPVALUE function.
I have 2 tables. Table 1 contains a column named 'Employee ID', and a column named 'Employee's Shift' that I am trying to populate by looking up the shift from Table 2.
Table 2 contains a column named 'Shift', and a column named 'Person Number' with values that are the same as Table 1's 'Employee ID'.
The caveat is that there are multiple entries for each Person Number in Table 2, although the 'Shift' will be consistent for all entries of that person number.
This should be a simple LOOKUPVALUE dax but none that I have tried are working. Can anybody help?
Hi leadacid ,
If the value you are trying to assign with lookup is the same for the key values for which you are doing lookup, lookup should work. If the values change; You will get an error like "expected single value".
Could you check if all shifts are the same for particular number_id of persons? What is the datatype of shift data? Sometimes, even if it says date, the data may be in datetime format. In other words, these data may differ from each other even if they appear the same.My second question is, is there a relationship between these two tables? Could you try connecting the ids in these two tables from the "Managing relationship" section?
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi leadacid ,
If the value you are trying to assign with lookup is the same for the key values for which you are doing lookup, lookup should work. If the values change; You will get an error like "expected single value".
Could you check if all shifts are the same for particular number_id of persons? What is the datatype of shift data? Sometimes, even if it says date, the data may be in datetime format. In other words, these data may differ from each other even if they appear the same.My second question is, is there a relationship between these two tables? Could you try connecting the ids in these two tables from the "Managing relationship" section?
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you! This worked:
Employee's Shift = LOOKUPVALUE('Employee Profile'[Shift],'Employee Profile'[Person Number],'Average Earning'[ID])
Thanks for your reply OguzMavice. Fortunately i was able to figure it out, it was a syntax error.
