Check if value is in another table and add columns in Power BI

Question

I have 2 tables, table1 contains some survey data and table2 is a full list of students involved. I want to check if Name in table2 is also found in table1. If yes, add Age and Level information in table2, otherwise, fill these columns with no data.

table1:

id  Name  Age  Level 
32  Anne  13   Secondary school 
35  Jimmy 5    Primary school
38  Becky 10   Primary school
40  Anne  13   Secondary school

table2:

id  Name   
1   Anne   
2   Jimmy    
3   Becky    
4   Jack

Expected output:

id  Name   Age      Level 
1   Anne   13       Secondary school 
2   Jimmy  5        Primary school
3   Becky  10       Primary school
4   Jack   no data  no data

Update:

I created a relationship between table1 and table2 using the common column id(which can be repeated in table1).

Then I used:

Column = RELATED(table1[AGE])

but it caught error:

The column 'table1[AGE]' either doesn't exist or doesn't have a relationship to any table available in the current context.

Create a relationship using Name please because names are common between those two tables not the ID column — user8078111

user8078111 user8078111 · Accepted Answer · 2021-06-01T06:20:48

There are various ways to achieve the desired output, but the simplest of them I found is to use the RELATED DAX function. If you found this answer then mark it as the answer.

Create a relationship between table1 and table2 using 'Name` column.

Create a calculated column in table2 as:

Column = RELATED(table1[AGE])
Repeat the same step for the Level column also.

Column 2 = RELATED(table1[LEVEL])

This will give you a table with ID, Name, Age, and Level for the common names between the two tables.

Now to fill those empty rows as no data, simply create another calculated column with following DAX:

Column 3 = IF(ISBLANK(table2[Column]), "no data", table2[Column])

Column 4 = IF(ISBLANK(table2[Column 2]), "no data", table2[Column 2])
This will give you the desired output.

EDIT:- You can also use the following formula to do the same thing in a single column

     Column = 
     VAR X = RELATED(table`[AGE])
     VAR RES = IF(ISBLANK(X), "no data", X)

     RETURN
         RES

and

    Column 2 = 
    VAR X = RELATED(table1[LEVEL])
    VAR RES = IF(ISBLANK(X), "no data", X)

    RETURN 
       RES

This will also give you the same output.

Check if value is in another table and add columns in Power BI

1 Answers