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 ...
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 )

     

     

     

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