Measure to compare values in two different table

[gauravnarchal]

Hello, I need help to check if the numbers in table 1 are available in table 2 and find out the ones that do not exist in table 2. True / False.

I need this as measure and don't want to create a calculated column,

Table 1

Number
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Table 2

Number
1
3
7
8
10
12
13
15

Result

Number	Result
1	TRUE
2	FALSE
3	TRUE
4	FALSE
5	FALSE
6	FALSE
7	TRUE
8	TRUE
9	FALSE
10	TRUE
11	FALSE
12	TRUE
13	TRUE
14	FALSE
15	TRUE

[bigchippah]

@v-yalanwu-msft, @Anonymous, @Tahreem24, @amitchandak 

Not to piggyback, but I have an additional question to the above solutions.

Table 1 is an employee roster. (Employee ID, name, work location)

Table 2 is a list of employees who have been audited (Employee ID, name, date audited)

The measures above show me a True/False if an employee appears on Table 2 (the audit list).  However, I would like to use a slicer to filter by work location.  Currently, my slicer returns the entire list of employees (with the correct True/False result ) instead of only those who are assigned to the sliced/selected work location.

Any ideas?

[v-yalanwu-msft · Accepted solution]

Hi,  @gauravnarchal ;

Try it.

Result = IF(MAX('Table1'[Number]) in VALUES(Table2[Number]),"TRUE","FALSE")

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hey, 
You could write measure like this to get the solution required.

Let me know if this works!

[Tahreem24]

@gauravnarchal Join the both table using relationship. Then create a measure in table 1:

Match = IF(SUM(Table1[Number]) IN {SUM(Table2[Number])}, TRUE(),FALSE())

[amitchandak]

@gauravnarchal , A measure to me used with table 1 Number

Measure = if(isblank(COUNTX(filter(Table1, Table1[Number] in values(Table2[Number])),Table1[Number])), FALSE(),TRUE())