Vlookup - returning a Corresponding Value from Parameter Table

[YavuzDuran]

Hi All, 

I have a parameter table as shown below:

And all need is to match the calculated cash down % per Location for a year/month with the above cash % limits and return the corresponding $Com (Column H). 

I excel it is way easier, in Power BI I tried such a solution below

Is there any simpler way that you can suggest to me 

Thank you

Location Manager - Cash Down % Commission =

if(SalesCommission[Cash Down % - Calculated]<calculate(sum('Parameters-LocationManager'[Cash %]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=1),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])),0,

if(SalesCommission[Cash Down % - Calculated]<calculate(sum('Parameters-LocationManager'[Cash %]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=2),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])),calculate(sum('Parameters-LocationManager'[$ Com]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=1),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])),

if(SalesCommission[Cash Down % - Calculated]<calculate(sum('Parameters-LocationManager'[Cash %]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=3),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])),calculate(sum('Parameters-LocationManager'[$ Com]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=2),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])),

if(SalesCommission[Cash Down % - Calculated]<calculate(sum('Parameters-LocationManager'[Cash %]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=4),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])),calculate(sum('Parameters-LocationManager'[$ Com]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=3),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])),

if(SalesCommission[Cash Down % - Calculated]<calculate(sum('Parameters-LocationManager'[Cash %]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=5),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])),calculate(sum('Parameters-LocationManager'[$ Com]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=4),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])),

calculate(sum('Parameters-LocationManager'[$ Com]),filter('Parameters-LocationManager','Parameters-LocationManager'[Cash% Index]=5),filter('Parameters-LocationManager','Parameters-LocationManager'[Year-Month-Branch]=SalesCommission[Year-Month-Branch])))))))

[v-shex-msft · Accepted solution]

Hi @YavuzDuran,

I modify your formula and tried to simplify these expressions, you can try to use the below formula if it helps:

Location Manager - Cash Down % Commission =
VAR filtered =
    FILTER (
        'Parameters-LocationManager',
        'Parameters-LocationManager'[Year-Month-Branch] = SalesCommission[Year-Month-Branch]
    )
VAR currIndex = 'Parameters-LocationManager'[Cash% Index]
VAR currCashDown = SalesCommission[Cash Down % - Calculated]
VAR _cashIndex1 =
    CALCULATE (
        SUM ( 'Parameters-LocationManager'[Cash %] ),
        FILTER ( filtered, [Cash% Index] = 1 )
    )
RETURN
    IF (
        currIndex = 1,
        IF ( currCashDown < _cashIndex1, 0 ),
        IF (
            currCashDown
                < CALCULATE (
                    SUM ( 'Parameters-LocationManager'[Cash %] ),
                    FILTER ( filtered, [Cash% Index] = currIndex )
                ),
            CALCULATE (
                SUM ( 'Parameters-LocationManager'[$ Com] ),
                FILTER ( filtered, [Cash% Index] = currIndex - 1 )
            ),
            CALCULATE (
                SUM ( 'Parameters-LocationManager'[$ Com] ),
                FILTER ( filtered, 'Parameters-LocationManager'[Cash% Index] = 5 )
            )
        )
    )

Regards,

Xiaoxin Sheng

[YavuzDuran]

Thank you @v-shex-msft  Will try

[Greg_Deckler]

@YavuzDuran Generally you would use LOOKUPVALUE or MAXX(FILTER(...),...). 

If you stay with your current solution, I highly recommend SWITCH(TRUE(),...) vs nested IF statements.

[YavuzDuran]

@Greg_Deckler will try. Thank you