Forum Discussion
Lookupvalue when Value doesn't exist in One Table
Hi All,
I have two tables - A CSR and a Milestones Table. They are joined on the CSR field.
Some CSRs don't exist in the Milestones table (See column 3 in the screenshot - CSR-0329 does not exist in the Milestones table).
I'm trying to create a calculated column using lookupvalue to get the AHOP Issued Date for a CSR from it's "Primary CSR".
For example, for CSR-0329 the AHOP Issued Date should be the same as it's Primary CSR (CSR-0007) - 21/05/2018, but it returns blank, because the Primary CSR in the Milestones Table doesn't exist.
This is my Calculated Column. How do I update this so that it returns 21/05/2018 for CSR-0329.
Lookup Column =
LOOKUPVALUE (
'Milestones'[AHOP Issued Date],
CSR[CSR], 'Milestones'[Primary CSR]
)
4 Replies
Hi kartiklal70 ,
In your case, you can create a calculated column like
Lookup Column = IF ( CSR[CSR] = "CSR-0329" && 'Milestones'[Primary CSR] = "CSR-0007", 'Milestones'[AHOP Issued Date], LOOKUPVALUE ( 'Milestones'[AHOP Issued Date], CSR[CSR], 'Milestones'[Primary CSR] ) )Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous ,
I don't want to hardcode it as there are other such cases in my dataset.
Hi kartiklal70 ,
You'd better provide some sample data of your two tables, along with the expected results.
I need more details.
Is there a relationship between the two tables?
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
