cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RvdHeijden Member
Member

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 ?

1 ACCEPTED SOLUTION

Accepted Solutions

Re: LookUp function ?

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 13
Super User
Super User

Re: LookUp function ?

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




RvdHeijden Member
Member

Re: LookUp function ?

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

Re: LookUp function ?

Does anyone know how to fix this problem ?

Any help is welcome

Re: LookUp function ?

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

Re: LookUp function ?

@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

Re: LookUp function ?

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

Re: LookUp function ?

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

Community Support Team
Community Support Team

Re: LookUp function ?

Hi @RvdHeijden,

 

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

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
RvdHeijden Member
Member

Re: LookUp function ?

@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