Doing calculation similar to VLOOKUP in Excel

[kbhalll]

I am trying to compute a field by matching column Focus Number’s (values like SA-DE105-0000005336) values with another column ‘SA number’ from another table RBIT. If the Focus Number value matches with SA number column, then Focus Number’s values will be retrieved in calculated field. But I am getting blank for the calculated column where there should be a value in Power BI. There are matching values in the both columns mentioned above, I have just given a values’ example above.

Currently I am using the formula below, but it’s not working as mentioned before:

SA number_cal =

VAR optr = TRIM([Focus Number])

RETURN IF(

    optr IN SELECTCOLUMNS(RBIT, "inv", TRIM(RBIT[SA number])),

    [Focus Number],

    BLANK()

)

I have tried a different calculation as well. But it doesn't seems to work.

Would really appreciate any insight that could help solve it. Thanks

[kbhalll · Accepted solution]

I found the root cause of this formula not working, it was actually in another formula not working correctly. Its all working now.

[Bibiano_Geraldo]

Hi @kbhalll ,

To achieve your need, you can use the LOOKUPVALUE function as shown bellow.

Make sure to replace column and table names with your owns:

SA number_cal = 
VAR optr = TRIM([Focus Number])
VAR MatchFound = 
    LOOKUPVALUE(
        RBIT[SA number],
        RBIT[SA number], optr
    )
RETURN 
MatchFound

[nidhigk]

SA number_cal =
VAR optr = TRIM ( [Focus Number] )
RETURN
IF (
CONTAINS (
RBIT,
RBIT[SA number], optr
),
optr,
BLANK ()
)
Please see if this works. 

[v-saisrao-msft]

Hi @kbhalll,

Have you had a chance to review the solution we shared by @Kedar_Pande @cengizhanarslan @MasonMA @vaibhavmahajan ? If the issue persists, feel free to reply so we can help further.

Thank you.

[vaibhavmahajan]

Hi @kbhalll ,

I hope you are doing well today 🙂❤️

Below are 3 approaches you can try to resolve the VLOOKUP-like issue in your Power BI dashboard/report when matching Focus Number with SA number.

Solution 1: Use LOOKUPVALUE

This is the most reliable approach for calculated columns when matching values across tables.

SA number_cal =
VAR MatchValue =
    LOOKUPVALUE (
        RBIT[SA number],
        RBIT[SA number], TRIM ( [Focus Number] )
    )
RETURN
IF (
    NOT ISBLANK ( MatchValue ),
    [Focus Number],
    BLANK ()
)

Solution 2: Handle hidden spaces / formatting issues

Sometimes values look identical but contain non-breaking spaces.

Use SUBSTITUTE + UNICHAR(160) to remove non-breaking spaces.

SA number_cal =
VAR FocusClean =
    SUBSTITUTE ( TRIM ( [Focus Number] ), UNICHAR(160), "" )

VAR MatchValue =
    LOOKUPVALUE (
        RBIT[SA number],
        RBIT[SA number],
        SUBSTITUTE ( TRIM ( RBIT[SA number] ), UNICHAR(160), "" )
    )
RETURN
IF ( NOT ISBLANK ( MatchValue ), [Focus Number] )

Solution 3: Use a relationship + RELATED (Best practice)

If there is a relationship between the two tables:

Create a relationship between

Focus[Focus Number] → RBIT[SA number]

Then use:

SA number_cal = RELATED ( RBIT[SA number] )

This is the cleanest and most performant solution.

Summary RCA and Fix:

Calculated column returning BLANK → Use LOOKUPVALUE

Hidden spaces → Use UNICHAR(160) cleanup

Relationship exists → Use RELATED

IN + SELECTCOLUMNS → Avoid for calculated columns

If this answer helped, kindly give Kudos and mark it as the Accepted Solution

to help other members find it more quickly.

Best regards,

Vaibhav Mahajan

LinkedIn: https://www.linkedin.com/in/vaibhavnmahajan

[MasonMA]

Hi, 

I'd recommend looking at this video How to Perform VLOOKUP in Power BI . 

In Power BI, the best practice for VLOOKUP would be to create a relationship from your fact table’s [Focus Number] to RBIT[SA number] (Text to Text), then use RELATED() in a calculated column. 

[cengizhanarslan]

This usually happens because IN SELECTCOLUMNS(...) is very sensitive to data type / hidden characters / row context, especially in a calculated column.

If your goal is “VLOOKUP-style: return Focus Number only when it exists in RBIT[SA number]”, do it like this instead:

SA number_cal =
VAR v = TRIM ( 'Table'[Focus Number] )
VAR hit =
    CALCULATE (
        COUNTROWS ( RBIT ),
        FILTER ( RBIT, TRIM ( RBIT[SA number] ) = v )
    )
RETURN
IF ( hit > 0, v, BLANK() )

[Kedar_Pande]

@kbhalll 

Your current DAX is over‑complicated for a straight “VLOOKUP‑style” match.

Use LOOKUPVALUE (or a relationship + RELATED) instead of IN + SELECTCOLUMNS.

If this answer helped, please click Kudos or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande