How to lookup existence of string in another table

[Anonymous]

Hi, I have been struggling with this for a few days.

Table 1                                   Table 1

Name                                      Name

abc123                                    hhh223

bty465                                    xyz543

hhh223                                   gfh891

bty876                                    tyu765

gfh891                                    ljk723

=========================

The goal is to display the values that are in 'Table 1'[Name] that do NOT exist in 'Table 2'[Name].

I keep getting errors when attempting to do what others have done for similar issues. The errors tell me that functions like 'CALCULATE' don't exist.

Thanks in advance.

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the link down below and the dax measure.

In Table1 NotIn Table2 =
VAR newtable =
EXCEPT ( VALUES ( Table1[Name] ), VALUES ( Table2[Name] ) )
RETURN
CONCATENATEX ( newtable, Table1[Name], ", " )

https://www.dropbox.com/s/kw68ijsh49nosyc/needshelp.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Anonymous]

This is exactly what I needed! However, how do I populate this into a new column? there will eventually be thousands of entries in this specific report. Thank you!

[Jihwan_Kim]

Hi, @Anonymous 

please try the below for the calculated column for Table1.

Only in Table1 =

IF( Table1[Name] in ALLSELECTED(Table2[Name]), "False", "True")

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Anonymous]

even as a new column in Table 1, where the column contains a TRUE/FALSE or a YES/NO will work with a slicer

[MFelix]

Hi @Anonymous ,

Check the posts below that present several option for doing this, the last one is my personal approach making an adaptation of the 2 previous posts:

https://dax.tips/2019/07/05/dax-pivot-text-into-a-list-of-words/

https://powerpivotpro.com/2014/01/containsx-finding-if-a-value-in-table-1-has-a-matching-value-in-table-2/v

https://community.powerbi.com/t5/Quick-Measures-Gallery/Find-Words-on-sentence-from-another-selected-sentence/m-p/1602658#M658

[amitchandak]

@Anonymous , Create this measure and plot with table1[name] in a visual

Calculate(count(Table1[Name]) , filter(Table1, not(Table1[Name] in allselected(table2[name]))))