Forum Discussion

eryan123's avatar
eryan123
Frequent Visitor
5 years ago

How do I validate that an ID exists in another table?

Hello All, 

I have a table that has multiple ID in one table. For example, one table will have the ID multiple times with different data. 

IDData_1Data_2
1  
1  
2  
2  
2  

 

The other table is much smaller and only has the ID once. 

IDData_3Data_4
1  
2  
3  
4  
5  


I would just like to return "Yes" if it does. "No" if it doesn't. I would like to do this with a calculated column so I have the ability to filter. 

Thank you in advance! 

Calculated columns
DAX
Table
Table Column

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    5 years ago

    create a one to many relationship by ID

    NewColumn=if(countrows(relatedtable(table1)),1,0)

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    5 years ago

    Hi eryan123 

     

    The wdx223_Daniel can cover your request with the relation.

    You can also create a calculated column with this code in Unique Table:

    validate = 
Var _Comm = LOOKUPVALUE('multiple ID'[ID],'multiple ID'[ID],'Unique ID'[ID])
return
if(ISBLANK(_Comm),"No","Yes")

     

    Output:

     

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

    Appreciate your Kudos✌️!!