topic Re: Non Matching values from another table in DAX Commands and Tips

Non Matching values from another table

Tob_P — Wed, 17 Jan 2024 10:37:55 GMT

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

Re: Non Matching values from another table

Anonymous — Thu, 18 Jan 2024 06:05:47 GMT

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.

Re: Non Matching values from another table

Tob_P — Thu, 18 Jan 2024 10:33:41 GMT

Anonymous

Thank you for this - worked perfectly for me.