Forum Discussion
LookUp function ?
I now have a table with unique names, the number of times that person called in sick and the total sickdays of that person all in 1 table.
Some people arent employed with us anymore so i want to look if someone is stille employed but that data is in another table.
So basically i want to search a name in a different table and get the value in a certain column and then get it back in my original table
in excel it would be vert.zoeken or a lookup function but what is it in DAX ?
Sorry for not being clear. The table in my previous reply was an example of a lookup table that would return an error.
From the MSDN website, about the behaviour of LOOKUPVALUE in case of duplicates:
"If multiple rows match the search values and in all cases result_column values are identical then that value is returned. However, if result_column returns different values an error is returned."
In other words, you are trying to answer the question whether Jan is employed. LOOKUPVALUE will work fine if it finds the same answer twice, but will return an error if finds two different answers.
So, the main issue is you have ambiguity in your real data, and the first question is how you want to handle this ambiguity.
A business rule to solve this issue could be the following: employee X must be considered employed as long as there is no record in Verzuimloop where Employed = 0.
In that case, you could filter Verzuimloop accordingly and check that LOOKUPVALUE returns BLANK.
13 Replies
Hi RvdHeijden,
If you are refering to the Power BI in the report view try to use the formula:
LOOKUPVALUE(Result_ColumName,Search_ColumnName1,Search_Value1)
Regards
MFelix
I found the formula, but with my current table if gives an error:
A table of multiple values was supplied where a single value was expected.
That error is right because in the table where im searching the names aren't unique so how do i fix this problem ?
Does anyone know how to fix this problem ?
Any help is welcome
