LOOKUPVALUE error confusingly occurs in one formula, but not other identical formulas

[Etx]

I'm very new to PowerBI, and having trouble getting a DAX formula to work correctly. In addition to helping fix the problem, I'm also open to suggestions on how to make a better formula in general, in case you think it looks a little inelegant 🙂  

I have 3 tables: Table1, Table2, and Table3. Each table has 3 columns, but I only care about the first column, called [Name]. The [Name] column has no duplicates within a table, but there can be the same [Name] in 2 or more Tables. The tables are not related to each other.

So for example:

Table 1

Name*

1stName

2ndName

3rdName

Table 2

Name*

1stName

2ndName

4thName

Table 3

Name*

1stName

3rdName

5thName

I want to create a single table that lists all the [Name]s from each table once in the first column, and then 3 boolean columns which say whether the exact [Name] can be found in that table. For example:

BoolTable

AllNames*	InTable1?	InTable2?	InTable3?
1stName	TRUE	TRUE	TRUE
2ndName	TRUE	TRUE	FALSE
3rdName	TRUE	FALSE	TRUE
4thName	FALSE	TRUE	FALSE
5thName	FALSE	FALSE	FALSE

For the 'BoolTable'[AllNames] column, I made a DISTINCT UNION of the 3 original tables from their [Name] columns.

Then, I made calculated columns for each of the 3 tables:

InTable1? = IF(Not(ISBLANK(LOOKUPVALUE('Table1'[Name],'Table1'[Name],'BoolTable'[AllNames]))),true,false)

InTable2? = IF(Not(ISBLANK(LOOKUPVALUE('Table2'[Name],'Table2'[Name],'BoolTable'[AllNames]))),true,false)

InTable3? = IF(Not(ISBLANK(LOOKUPVALUE('Table3'[Name],'Table3'[Name],'BoolTable'[AllNames]))),true,false)

So, basically, each calculated column will look in each 'BoolTable'[AllNames] row and compare the 'TableX'[Name] value in their respective table, and return that 'TableX'[Name] (I don't actually care what it returns, just if it is found in that table or not).

The calculated columns 'BoolTable'[InTable2?] and [InTable3?] work perfectly fine, and don't give errors. However, [InTable1?] will give the following error: "A table of multiple values was supplied when a single value was expected"… and then it breaks all my measures and visuals.

I don't understand why this happens, because A. There are no duplicate values in 'Table1'[Name], and B. It works perfectly in the other 2 calculated columns.

Any advice?

Thank you,

Ethan

[amitchandak]

@Etx , You have union distinct names from all table. In power query append and delete duplicates. In DAX Distinct and union

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

In dax you have following was to get data from one table to another

https://www.youtube.com/watch?v=czNHt7UXIe8

[Etx · Accepted solution]

Thanks everyone for your help!

Ultimately, I found the solution to my problem in this thread.

TL;DR, because the LOOKUPVALUE was finding duplicate values in the destination column (for some reason?), I had to use FIRSTNONBLANK()