Forum Discussion

ljohnson's avatar
ljohnson
Regular Visitor
6 years ago
Solved

Vlookup with IF THEN

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!

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    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 )

     

     

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    6 years ago

    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.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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.

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    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 )