LookUp function ?

[RvdHeijden]

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 ?

[MFelix]

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

[RvdHeijden]

@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 ?

[RvdHeijden]

Does anyone know how to fix this problem ?

Any help is welcome

[LaurentCouartou]

You can try and use the TOPN or SAMPLE functions.

Note the TOPN may return more than one value depending on the sorting criteria used.

[RvdHeijden]

@LaurentCouartou

When i check the discription of both formulas i dont see why i would use them in this case.

I want to check if a person is 'employed' yes or no in a table where the name isn't unique.

i have one table where the names are unique and already have a few calculated colums such as 'Times called in Sick' and 'Total Sickdays'

Now i want another column that checks if he is still employed, i tried to fix this using the relationships but i can't get one doing due to ambiguity

[LaurentCouartou]

I thought you were having trouble with the search_value argument or your LOOKUPVALUE expression (where you would pass many values when only one was expected). In that case, you could wrap the search_value (column) expression in a TOPN or a SORT expression to make it only returns one value.

If, however, the error comes from the LOOKUPVALUE finding many matches for the input criteria, then neither of these functions will help. You would have to rewrite your expression to make certain it only returns one value.

Depending on your lookup table, searching on several columns may solve the issue.

If you only need to perform an existence check, you may also do without LOOKUPVALUE and simply evaluate a COUNTROWS with the appropriate filter context.

[RvdHeijden]

@LaurentCouartou

I have a table (Aantal keren ziek) where the names are unique and i want to use the LOOKUPVALUE of the unique name in a table (Verzuimverloop) where the name is NOT unique and return the value in the colum 'In dienst'.

Because the names aren't unique in the table im looking in it returns the error:

A table of multiple values was supplied where a single value was expected.

The value per person is always the same but a person can be in the list more then once.

[v-yulgu-msft]

Hi @RvdHeijden,

Can you share us some sample data so that we can test for you?

Regards,
Yuliana Gu

[RvdHeijden]

@v-yulgu-msft@LaurentCouartou

Sample data is very simple

Table 1 = Verzuimverloop

Column A       Column B

(name)             (Employed)

Piet                       1

Jan                        1

Jan                        1

Piet                       1

Frits                      0

*Employed: 1= 'Yes' and 0 = 'No'

Table 2 = Aantal keren ziek

Column A       Column B

(name)             (Employed)

Piet                       1/0

Jan                        1/0

Frits                       1/0

The table 'Aantal keren ziek' should have the Lookupvalue formula but when i search for the name in 'Verzuimverloop' it finds the name more then once hence the error

[LaurentCouartou]

LOOKUPVALUE did not return any error on your data because duplicate entries all return the same results.

It would not work if, for example, with the following data:

name	employed
Jan	1
Jan	0

What is your business rule to handle inconsistencies?

[RvdHeijden]

@LaurentCouartou

I dont understand what you are saying, the lookupvalue does give an error.

I understand what your saying but my tables aren't made up that way

In this table the names are unique but in the table where i want the lookupvalue to look the names aren't unique

I thought this would be a simple question but it seems harder then i thought

[LaurentCouartou · Accepted solution]

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.

[RvdHeijden]

@LaurentCouartou

you were right, i noticed there were different values when i checked the data.

I thought those values were automated from our system but apperantly someone has to fill them manually hence the different values.