Replicating the functionality of XLOOKUP in PowerBI with IF Condition

[Pratiksha1]

Hi all, 

I am new to PowerBI and I am facing a challenge with replicating the Excel XLOOKUP function combined with an IF statement in PowerBI. This is what my data looks like - 

Now I have tried to create the Phase_Match column using the formula 

Phase_Match = IF(Sheet1[Issue_Type] = "CO Deliverable", IF(CONTAINS(Sheet1,Sheet1[Issue_Key],[Parent_Link]) = TRUE(),Sheet1[Milestone_Phases],"NO MILESTONE MATCH"),Sheet1[Milestone_Phases])

This is looking up and fetching values but it is not quite replicating the XLOOKUP functionality. I want it to lookup Parent_Link in Issue_Type and fetch the corresponding Milestone_Phase where it gets the first lookup match. This is what I want it to give me - 

I have tried using LOOKUPVALUE but it keeps throwing the error - "A table of multiple values was supplied where a single value was expected." I tried this - LOOKUPVALUE(Sheet1[Milestone_Phases],Sheet1[Issue_Key],[Parent_Link])

I have also tried Merge Queries in Power Query but I'm not sure how to combine that with my IF condition.

Any help on this will be greatly appreciated. Thanks in advance!

[v-yanjiang-msft · Accepted solution]

Hi @Pratiksha1 ,

According to your description, the overall logic of your formula is correct, but the highlight part will always return the Milestone_Phased in the current row instead of the first correponding row as your expected.

Here's my solution,

1.In Power Query, add an index column.

2.Create a calculated column.

Phase_Match =
IF (
    Sheet1[Issue_Type] = "CO Deliverable",
    IF (
        CONTAINS ( Sheet1, Sheet1[Issue_Key], [Parent_Link] ) = TRUE (),
        VAR _Index =
            MINX (
                FILTER ( 'Sheet1', 'Sheet1'[Issue_Key] = EARLIER ( 'Sheet1'[Parent_Link] ) ),
                'Sheet1'[Index]
            )
        RETURN
            MINX (
                FILTER ( 'Sheet1', 'Sheet1'[Index] = _Index ),
                'Sheet1'[Milestone_Phases]
            ),
        "NO MILESTONE MATCH"
    ),
    Sheet1[Milestone_Phases]
)

Get the correct result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[Pratiksha1]

Hi @v-yanjiang-msft ,

It worked perfectly! Thanks a bunch!