Non Matching values from another table

[Tob_P]

This is my relationship between 2 tables, linked by an ID. The Opportunity table will hold all of the ID values from the Bidding Con table, but also hold a large number of ID values that are not on the Bidding Con table and I want to create a calculated column that will display as either true/false or a 0/1 or 'Has ID'/'Does Not Have ID', those values NOT in the Bidding Con table.

I have tried LOOKUPVALUE but that returns an error...

I have tried to merge the tables but then that will only return those records/values that match and I have also tried a variety of RELATED solutions such as =IF(ISBLANK(RELATED(...but this will not work I believe due to the nature of the relationship between the two tables.

Can anyone recommend a potential solution please?

Thanks

[Anonymous · Accepted solution]

Hi @Tob_P ,

Please follow these steps:

You are using LOOKUPVALUE to return an incorrect value because an Opportunity (tober_opportunityid) in the Bidding Con table may have more than one corresponding value, and the result of the Calculation Column will only show one value, so the Calculation Column doesn't know which value to get at this time. So you can refer to the following solution:

Based on your description, I created two tables and the relationship between them as follows.

1.Create a calculated column in the Opportunity table and write the following formula.

Column =

var _select=

SELECTCOLUMNS(FILTER(ALL('Bidding Con'),'Bidding Con'[Opportunity(tober_opportunityid)]=EARLIER('Opportunity'[opportunrityid])),"column",[Value])

return

IF(

    'Opportunity'[Value] in _select,1,0)

2.The result obtained is shown below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[Tob_P]

@Anonymous 

Thank you for this - worked perfectly for me.