DAX VLOOKUP custom column with duplicate values and blanks

[tim_001]

Hi, 

I have pretty much the same issues as I found in this post: 

hi all,

i am new to PowerBI and am facing an issue, any comments or help is much appreciated.

i have 2 tables at the moment, table 1 and table 2 which looks like this:

Table1

type 1 , apple

type 1, apple

type 2, cheese

type 3, bake

type 3, bake

Table2

type 1 , chef1

type 1, chef2

type 2, cook

type 3, helper1

type 3, helper2

what i would like to achieve is below:

Table1

type 1 , apple , chef1

type 1, apple , chef1

type 2, cheese , cook

type 3, bake , helper1

type 3, bake, helper1

basically a 'vlookup' from table1 to table2 taking only the first value.

i have tried LOOKUPVALUE but it returns an error of 'multiple values was supplied where a single value was expected'

So I followed the accepted solution: 

Try a new column in table 1

minx(filter(Table2, Table1[type] = Table2[Type] ) , Table2[Value] )

But whenever there is both a blank cell and a full cell under Table2[Value], it returns the blank cell instead of the full cell. 

--> Is there any way to tweak this formula to return the cell with data instead of blank if both are available with same filter?

Any help is much appreciated 🙂 

BR
Tim

[Shahid12523]

Use this instead of your formula to ignore blanks:

NewColumn =
MINX (
FILTER (
Table2,
Table2[Type] = Table1[Type]
&& NOT ISBLANK ( Table2[Value] )
),
Table2[Value]
)

👉 This skips blanks and returns the first non-blank (lowest alphabetically).

If you just want any non-blank:

NewColumn =
FIRSTNONBLANK (
FILTER ( Table2, Table2[Type] = Table1[Type] ),
Table2[Value]
)

[Royel · Accepted solution]

Hi @tim_001  you can try this one. Select your Table1 and create a calculated column  

Chef = 
VAR CurrentType = Table1[Type]
RETURN
CALCULATE(
    FIRSTNONBLANK(Table2[Item], Table2[Item]),
    FILTER(
        Table2, 
        Table2[Type] = CurrentType && 
        NOT(ISBLANK(Table2[Item])) &&
        Table2[Item] <> ""
    )
)

Results:

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

[tim_001]

Hi @rajendraongole1  - thanks for the fast reply - much appreciated 🙂

I tried above, but it still returns a blank - maybe updated table below for clarifaction: 

Table1

type 1 , apple

type 1, apple

type 2, cheese

type 3, bake

type 3, bake

Table2

type 1 , 

type 1, chef1

type 1, chef1

type 2, cook

what i would like to achieve is below:

Table1

type 1 , apple , chef1

type 1, apple , chef1

type 2, cheese , cook

..> Right now it returns blank to me instead of chef1

[rajendraongole1]

you can try the below logic, still any please find the attached pbix.

Chef_2 =

MINX (

    FILTER (

        Table2,

        Table2[Type] = Table1[Type] &&

        NOT (

            ISBLANK ( Table2[Value] ) || Table2[Value] = ""

        )

    ),

    Table2[Value]

)

[Ashish_Mathur]

Hi,

This calculated column formula in Table1 works

Column = CALCULATE(MIN(Table2[Fruit]),FILTER(Table2,Table2[Type]=EARLIER(Table1[Type])&&Table2[Fruit]<>BLANK()))

Hope this helps.

[rajendraongole1]

Hi @tim_001  - you can create a calculated column as like below:

Chef =

MINX (

    FILTER (

        Table2,

        Table2[Type] = Table1[Type] &&

        NOT ISBLANK ( Table2[Value] )

    ),

    Table2[Value]

)

Hope this helps.