IF ELSE Logic

Hi it_akasjraj ,

According to your description, I create a sample.

Assignment table:

Sheet1 table:

Here's my solution, create a calculated column in Assignment table.

Column =
VAR _T =
    FILTER (
        'Sheet1',
        NOT ( ISBLANK ( 'Sheet1'[LOWER LIMIT] ) )
            && 'Sheet1'[BANK] = EARLIER ( 'Assignment'[Payment_Mode] )
    )
VAR _T2 =
    FILTER (
        'Sheet1',
        ISBLANK ( 'Sheet1'[LOWER LIMIT] )
            && 'Sheet1'[BANK] = EARLIER ( 'Assignment'[Payment_Mode] )
    )
RETURN
    IF (
        'Assignment'[Actual_Sale_Amount] >= MAXX ( _T, 'Sheet1'[LOWER LIMIT] )
            && 'Assignment'[Actual_Sale_Amount] <= MAXX ( _T, 'Sheet1'[UPPER LIMIT] ),
        MAXX ( _T, 'Sheet1'[TRANSACTION PERCENTAGE] ),
        MAXX ( _T2, 'Sheet1'[TRANSACTION PERCENTAGE] )
    )

Or a measure if you prefer.

Measure =
VAR _T =
    FILTER (
        'Sheet1',
        NOT ( ISBLANK ( 'Sheet1'[LOWER LIMIT] ) )
            && 'Sheet1'[BANK] = MAX ( 'Assignment'[Payment_Mode] )
    )
VAR _T2 =
    FILTER (
        'Sheet1',
        ISBLANK ( 'Sheet1'[LOWER LIMIT] )
            && 'Sheet1'[BANK] = MAX ( 'Assignment'[Payment_Mode] )
    )
RETURN
    IF (
        MAX ( 'Assignment'[Actual_Sale_Amount] ) >= MAXX ( _T, 'Sheet1'[LOWER LIMIT] )
            && MAX ( 'Assignment'[Actual_Sale_Amount] ) <= MAXX ( _T, 'Sheet1'[UPPER LIMIT] ),
        MAXX ( _T, 'Sheet1'[TRANSACTION PERCENTAGE] ),
        MAXX ( _T2, 'Sheet1'[TRANSACTION PERCENTAGE] )
    )

They all get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.