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 )