topic Re: Problem with LOOKUPVALUE not searching multiple columns in DAX Commands and Tips

Problem with LOOKUPVALUE not searching multiple columns

Anonymous — Tue, 18 Jul 2023 12:40:29 GMT

Am I right in saying that I cannot use the same Search_ColumnName twice when using the LOOKUPVALUE function?

I have 4 columns in one table with values that match the Search_ColumnName values in another table and I am trying to use the same Search_ColumnName for all 4 Search_Values and its is giving me blanks in the new column.

Example 1 (This works with the correct values in the new column)

Teamowner = LOOKUPVALUE(Owner[Owner],Owner[Team],S3SSL[tags2])

Example 2 (This one just gives me blanks in the new column)

Teamowner = LOOKUPVALUE(Owner[Owner],Owner[Team],S3SSL[tags1],Owner[Team],S3SSL[tags2],Owner[Team],[tags3],Owner[Team],S3SSL[tags4])

What is the best way to combine the 4 columns in the S3SSL table to one new column using the values in the Owner table?

Re: Problem with LOOKUPVALUE not searching multiple columns

johnt75 — Tue, 18 Jul 2023 15:49:25 GMT

If you know that there will only be 1 row which matches then you could use

Teamowner = SELECTCOLUMNS ( FILTER ( Owner, Owner[Team] IN { S3SSL[tags1], S3SSL[tags2], S3SSL[tags3], S3SSL[tags4] } ), Owner[Owner] )

Re: Problem with LOOKUPVALUE not searching multiple columns

Anonymous — Tue, 18 Jul 2023 22:53:37 GMT

Thank you for the reply but unfortunately that didnt work.

This is what I am trying to achieve

Re: Problem with LOOKUPVALUE not searching multiple columns

johnt75 — Wed, 19 Jul 2023 09:56:03 GMT

You need to filter out the blanks as well,

Teamowner = SELECTCOLUMNS ( FILTER ( Owner, Owner[Team] IN { S3SSL[tags1], S3SSL[tags2], S3SSL[tags3], S3SSL[tags4] } && NOT ISBLANK ( Owner[Team] ) ), Owner[Owner] )

Re: Problem with LOOKUPVALUE not searching multiple columns

Anonymous — Sun, 23 Jul 2023 22:59:39 GMT

Still no joy on this one so I ended up creating a conditional column, which also gave me errors until I added a character to the blank spaces in each column and then it gave me what I needed.

Re: Problem with LOOKUPVALUE not searching multiple columns

Anonymous — Sun, 23 Jul 2023 23:02:58 GMT

The problem is with the final parameter but it gives no expalanation as to what the issue might be.