Lookupvalue when Value doesn't exist in One Table

[kartiklal70]

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]
)

[v-stephen-msft]

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.

[kartiklal70]

@v-stephen-msft ,

I don't want to hardcode it as there are other such cases in my dataset. 

[v-stephen-msft]

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.

[kartiklal70]

@v-stephen-msft ,

Link to Sample Power BI Report.

https://www.dropbox.com/s/piq5xhqxwo1mmjd/Sample%20Power%20BI%20Report.pbix?dl=0

Expected Results for Calculated Column: