Check if Value Exits on another table/colum and display message.

[Anonymous]

I have table 1 that lists project ID's.  I have table 2 which has subset of Project IDs.  How do I check if the "selected project" from table 1 exists in table 2 and display a message if true. (ex: "Project Exists")

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may create calculated column in Table1 like DAX below.

Column1 = IF(ISBLANK(LOOKUPVALUE(Table2[ProjectID],Table2[ProjectID],Table1[ProjectID])) , BLANK(), "Project Exists")

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FrankAT]

Hi @Anonymous 

you can check this with Power Query if you merge these two tables with the inner join like this:

// Table 1
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAwVIrVAdEmUNoISltAaEMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t])
in
    Source

// Table 2
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjAwUYrVAdHGUNpCKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t])
in
    Source

// Projects
let
    Source = Table.NestedJoin(#"Table 1", {"Project"}, #"Table 2", {"Project"}, "Table 2", JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Table 2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Project", "Concurrent Project"}})
in
    #"Renamed Columns"

Regards FrankAT