How to use Vlookup to get the data from table A to table B in power bi?

[Shineccx007]

Hello!

I have two tables, table A:  SetReorderPoint(Sept), it has all the item no with category "set"

table B:Item_SetOrCalculate, it has more items no than table A, no category in this table, but I am creating a column to show the catefory. If the item no I can find from table A, show "set" under table B category, otherwise showed "Calculate" under table B catogory. I wrote the DAX below but it kept showing wrong. Can anyone help please?

====================================================================================

Category =

IF (

    ISBLANK (

        LOOKUPVALUE(

            'SetReorderPoint(Sept)'[Category],

            'SetReorderPoint(Sept)'[Item No], Item_SetOrCalculate[No]

        )

    ),

    "CALCULATE",

    LOOKUPVALUE(

        'SetReorderPoint(Sept)'[Category],

        'SetReorderPoint(Sept)'[Item No], Item_SetOrCalculate[No]

    )

)

[v-jialongy-msft · Accepted solution]

Hi @Shineccx007 

Based on your needs, I have created the following table

You can use the following dax to achieve what you want

New Column = 
IF(
    ISBLANK(LOOKUPVALUE('SetReorderPoint(Sept)'[category], 'SetReorderPoint(Sept)'[index], 'Item_SetOrCalculate'[index])),
    "calculate",
    "set"
)

Best Regards,

Jayleny

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

[Shineccx007 · Accepted solution]

Hi Jay,

Your explaination worked! Thank you so much!

[v-jialongy-msft · Accepted solution]

Hi @Shineccx007 

Based on your needs, I have created the following table

You can use the following dax to achieve what you want

New Column = 
IF(
    ISBLANK(LOOKUPVALUE('SetReorderPoint(Sept)'[category], 'SetReorderPoint(Sept)'[index], 'Item_SetOrCalculate'[index])),
    "calculate",
    "set"
)

Best Regards,

Jayleny

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

[Shineccx007 · Accepted solution]

Under table B will all items with category updated. I now have table C and 😧

For "set" items I need to vlookup the fixed quantity from table C, but for "calculate" items I need to get the data from calculation from table D.

What DAX shall I write. Any suggestion?

[Shineccx007 · Accepted solution]

Hi Jay,

Your explaination worked! Thank you so much!

[Greg_Deckler]

@Shineccx007 What is wrong? Can you provide sample information? I often have better luck using MAXX(FILTER(...),...) instead of LOOKUPVALUE. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Shineccx007]

Hi Greg,

Table A: SetReorderPoint(Sept), Item no from 1 to 10, category all is "set"

Table B:Item_SetOrCalculate, Item no from 1 to 50, no category column. I wanted to add one more column in this table. If the item no show on Table A, category is "set", else showed calculate.

What shall I write the DAX?

Table B