Vlookup with IF THEN

[ljohnson]

I need to create a measure that looks up data from one table against two different tables and returns values from associated columns based on date and what it finds.

For example: data from Table 1 is bumped up against Table 2, if it finds the related well completion e-key, it returns the shrink factor and NGL yield, otherwise it returns values from Table 3 as shown below:

Thank you!

[v-xuding-msft · Accepted solution]

Hi @ljohnson ,

Create two columns in table2:

Factor = 
CALCULATE (
    MAX ( 'Table 2'[MARKET SHRINK DRY FACTOR] ),
    FILTER (
        ALLEXCEPT ( 'Table 2', 'Table 2'[Well Completion Ekey] ),
        'Table 2'[Prod PED] = MIN ( 'Table 2'[Prod PED] )
    )
)

NGL YIELD = 
CALCULATE (
    MAX ( 'Table 2'[NGL YIELD GAL/MCF] ),
    FILTER (
        ALLEXCEPT ( 'Table 2', 'Table 2'[Well Completion Ekey] ),
        'Table 2'[Prod PED] = MIN ( 'Table 2'[Prod PED] )
    )
)

Create measures:

Factor 1 = 
VAR FACTOR_table2 =
    LOOKUPVALUE (
        'Table 2'[Factor],
        'Table 2'[Prod PED], MAX('Table'[Prod PED]),
        'Table 2'[Well Completion Ekey],MAX( 'Table'[Well Completion Ekey])
    )
VAR FACTOR_table3 =
    LOOKUPVALUE (
        'Table 3'[MARKET SHRINK DRY FACTOR],
        'Table 3'[Prod PED],  MAX('Table'[Prod PED])
    )
RETURN
    IF ( ISBLANK ( FACTOR_table2 ), FACTOR_table3, FACTOR_table2 )

NGL YIELD 1 = 
VAR NGLyield_table2 =
    LOOKUPVALUE (
        'Table 2'[NGL YIELD],
        'Table 2'[Prod PED], MAX('Table'[Prod PED]),
        'Table 2'[Well Completion Ekey], MAX('Table'[Well Completion Ekey])
    )
VAR NGLyield_table3 =
    LOOKUPVALUE (
        'Table 3'[NGL YIELD GAL/MCF],
        'Table 3'[Prod PED],MAX( 'Table'[Prod PED])
    )
RETURN
    IF ( ISBLANK ( NGLyield_table2 ), NGLyield_table3, NGLyield_table2 )

[amitchandak]

@ljohnson , refer this https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/

[Anonymous]

try this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\sprmnt21\Documents\PBI\tab123.xlsx"), null, true),
    Tabella1_Table = Source{[Item="Tabella1",Kind="Table"]}[Data],
    #"Added Custom" = Table.AddColumn(Tabella1_Table, "add", each (Tabella2{[PP=[PP],WCE=[WCE]]}?)??Tabella3{[PP=[PP]]}),
    #"Expanded add" = Table.ExpandRecordColumn(#"Added Custom", "add", {"MSDF"}, {"add.MSDF"})
in
    #"Expanded add"

PS

hope you can appreciate the answer even if there is no "IF... THEN..." 😁

you have just to change some label to adapt at you dataset

[Greg_Deckler]

@ljohnson - You should be able to replicate that functionality with LOOKUPVALUE and IF DAX statements.

This might help: https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991

Otherwise, 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.

[CNENFRNL]

Hi, there.

Pls paste the aforementioned 3 tables as mockup. It's way much easier and more concrete to show you the measure to manipulate table than a verbose, descriptive solution.