Power Bi Dax lookup query

[boa08jp]

Hi all,

I’m trying to calculate it so Power BI will check the value of the Outcome column in one csv file (the CSV File 2025 table). If the value is "LATE" or "FAILED," it will look up the corresponding Container value from the second csv file (CSV File Incident Log 2025 table) – where there might be duplicate values. Otherwise, it will return the original Outcome value. Currently, I’m using the below formula, but it keeps on retuning a syntax error no matter what alteration I make. Can anyone help please.

NewOutcomeColumn =

VAR OutcomeValue = 'CSV File 2025'[Outcome]

RETURN

SWITCH(

    TRUE(),

    OutcomeValue = "LATE", LOOKUPVALUE('CSV File Incident Log 2025'[Container], 'CSV File Incident Log 2025'[Outcome], "LATE"),

    OutcomeValue = "FAILED", LOOKUPVALUE('CSV File Incident Log 2025'[Container], 'CSV File Incident Log 2025'[Outcome], "FAILED"),

    OutcomeValue

).

[quantumudit · Accepted solution]

Hello @boa08jp 

Some additional explanation, along with a sample dataset and the expected results dataset or screenshot, would have been very helpful to understand your query better. However, based on my understanding the question, I have created a simple sample dataset as follows:

This is the screenshot of the CSV File 2025 table:

This is the screenshot of the CSV File Incident Log 2025 table:

Here is how the data model look like:

Given your query, there could be two scenarios:

- When you have relationship between the two tables

- When you don't have a relationship between the two tables

To address both scenarios, I have duplicated the CSV File Incident Log 2025 table and hence you also see the CSV File Incident Log 2025 (Unrelated) table.

Scenario - 1 : You have relationship between the two tables

If you have relationship between the two tables, then you can use the following DAX to create the calculated column in the CSV File 2025 table:

NewOutcomeColumn (Relationship Exists) = 
VAR _outcome = 'CSV File 2025'[Outcome]
RETURN
IF(
    _outcome = "LATE" || _outcome = "FAILED",
    CALCULATE(
        MAX('CSV File Incident Log 2025'[Container]),
        RELATEDTABLE('CSV File Incident Log 2025')
    ),
    _outcome
)

Scenario - 2 : You don't have relationship between the two tables

If you don't have relationship between the two tables, you can use the following DAX to create the calculated column in the CSV File 2025 table:

NewOutcomeColumn (Disconneced Table) = 
VAR _outcome = 'CSV File 2025'[Outcome]
RETURN
IF(
    _outcome = "LATE" || _outcome = "FAILED",
    LOOKUPVALUE('CSV File Incident Log 2025 (Unrelated)'[Container], 'CSV File Incident Log 2025 (Unrelated)'[Outcome], _outcome),
    _outcome
)

Here is the screenshot of final table with the two calculated columns addressing the two possible scenarios:

I am also attaching the Power BI file for your reference. However, let me know if you still face any issues.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Proud to be a Super User

[Anonymous · Accepted solution]

Hi @boa08jp ,

Thanks for the reply from quantumudit / bhanu_gautam .

You are looking for the first `Container` value in the `CSV File 2025` table that corresponds to the `CSV File Incident Log 2025` table when the value of the `Outcome` column is “LATE” or “FAILED”, right? in the `CSV File Incident Log 2025` table when the value in the `Outcome` column is “LATE” or “FAILED”, looks for the first `Container` value corresponding to the `CSV File Incident Log 2025` table, otherwise returns the original Outcome value. Is this correct?

Here is the data I used for testing:

'CSV File 2025' table:

Outcome
LATE
ON TIME
FAILED

'CSV File Incident Log 2025' table:

Container	Outcome
A123	LATE
A123	LATE
B456	LATE
C789	FAILED
C789	FAILED
D012	FAILED
D012	FAILED
E345	ON TIME
E345	ON TIME

Create a calculated column:

NewOutcomeColumn = 
VAR OutcomeValue = 'CSV File 2025'[Outcome]
RETURN
SWITCH(
    TRUE(),
    OutcomeValue = "LATE", 
    CALCULATE(
        FIRSTNONBLANK('CSV File Incident Log 2025'[Container], 1),
        FILTER('CSV File Incident Log 2025', 'CSV File Incident Log 2025'[Outcome] = "LATE")
    ),
    OutcomeValue = "FAILED", 
    CALCULATE(
        FIRSTNONBLANK('CSV File Incident Log 2025'[Container], 1),
        FILTER('CSV File Incident Log 2025', 'CSV File Incident Log 2025'[Outcome] = "FAILED")
    ),
    OutcomeValue
)

Or you can create a measure that does the same thing:

NewOutcomeMeasure = 
VAR OutcomeValue = SELECTEDVALUE('CSV File 2025'[Outcome])
RETURN
SWITCH(
    TRUE(),
    OutcomeValue = "LATE", 
    CALCULATE(
        FIRSTNONBLANK('CSV File Incident Log 2025'[Container], 1),
        FILTER('CSV File Incident Log 2025', 'CSV File Incident Log 2025'[Outcome] = "LATE")
    ),
    OutcomeValue = "FAILED", 
    CALCULATE(
        FIRSTNONBLANK('CSV File Incident Log 2025'[Container], 1),
        FILTER('CSV File Incident Log 2025', 'CSV File Incident Log 2025'[Outcome] = "FAILED")
    ),
    OutcomeValue
)

I would recommend measure, as it performs better than a calculated column while still accomplishing the same functionality.

CALCULATE() and FILTER() will ensure that a value is returned, while FIRSTNONBLANK() will deal with duplicate values, returning only the first value found.

The final page visualization is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[boa08jp]

Thank you for this!! 

if I needed to actually look at the duplicates. how would I change your above formulas? I've tried to take out the FIRSTNONBLANK but it keeps erroring.

thanks in advance

[quantumudit · Accepted solution]

Hello @boa08jp 

Some additional explanation, along with a sample dataset and the expected results dataset or screenshot, would have been very helpful to understand your query better. However, based on my understanding the question, I have created a simple sample dataset as follows:

This is the screenshot of the CSV File 2025 table:

This is the screenshot of the CSV File Incident Log 2025 table:

Here is how the data model look like:

Given your query, there could be two scenarios:

- When you have relationship between the two tables

- When you don't have a relationship between the two tables

To address both scenarios, I have duplicated the CSV File Incident Log 2025 table and hence you also see the CSV File Incident Log 2025 (Unrelated) table.

Scenario - 1 : You have relationship between the two tables

If you have relationship between the two tables, then you can use the following DAX to create the calculated column in the CSV File 2025 table:

NewOutcomeColumn (Relationship Exists) = 
VAR _outcome = 'CSV File 2025'[Outcome]
RETURN
IF(
    _outcome = "LATE" || _outcome = "FAILED",
    CALCULATE(
        MAX('CSV File Incident Log 2025'[Container]),
        RELATEDTABLE('CSV File Incident Log 2025')
    ),
    _outcome
)

Scenario - 2 : You don't have relationship between the two tables

If you don't have relationship between the two tables, you can use the following DAX to create the calculated column in the CSV File 2025 table:

NewOutcomeColumn (Disconneced Table) = 
VAR _outcome = 'CSV File 2025'[Outcome]
RETURN
IF(
    _outcome = "LATE" || _outcome = "FAILED",
    LOOKUPVALUE('CSV File Incident Log 2025 (Unrelated)'[Container], 'CSV File Incident Log 2025 (Unrelated)'[Outcome], _outcome),
    _outcome
)

Here is the screenshot of final table with the two calculated columns addressing the two possible scenarios:

I am also attaching the Power BI file for your reference. However, let me know if you still face any issues.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Proud to be a Super User

[quantumudit]

Hello @boa08jp 

Could you please provide sample data that fully represents your issue or question in a usable format, rather than a screenshot? Ensure the dataset is anonymized and does not contain any sensitive or unrelated information.

Additionally, it would be very helpful if you could share the expected outcome based on the provided data - this can be in any format, including a screenshot. Having a clear reference for comparison will significantly improve the chances of getting the correct solution in the first response.

For best practices, you may find the following links useful:

• Need help uploading data?
• Want faster answers?

Thanks,

Udit

[bhanu_gautam]

@boa08jp , Try using this

dax
NewOutcomeColumn =
VAR OutcomeValue = 'CSV File 2025'[Outcome]
RETURN
SWITCH(
TRUE(),
OutcomeValue = "LATE", LOOKUPVALUE('CSV File Incident Log 2025'[Container], 'CSV File Incident Log 2025'[Outcome], "LATE"),
OutcomeValue = "FAILED", LOOKUPVALUE('CSV File Incident Log 2025'[Container], 'CSV File Incident Log 2025'[Outcome], "FAILED"),
OutcomeValue
)