Forum Discussion

it_akasjraj's avatar
it_akasjraj
Frequent Visitor
4 years ago
Solved

IF ELSE Logic

 

Hi Community, Need expert advice.

Please find the attached screen shot for better understanding.

 

I have two table,

First table has columns "Actual_Sale_Amount" And "Payment_Mode"

Image 1"

 

Second table has columns "Bank", "Lower Limit", Upper Limit", and one more column is available in second table "Transaction Percentage"

 

I am trying to write a logic,

If in first table the "Actual Sale Amount" is greate then or equal to second table "Lower Limit" AND in first table the "Actual Sale Amount" is less then or equal to second table "Upper Limit", then we have to consider the value of "Transaction Percentage" from second table.

else have to take the value of "Transaction Percentage" where "Lower Limit" And "Upper Limit" is blank in second table.

 

Example:

if i have sell of 1900 INR, then the transaction percentage will be 0.003, else 0.006.

 

and we cant pass static value in if else condition, because in future there will be more rows get added in second table.

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

     

4 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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.

     

  • PC2790's avatar
    PC2790
    Community Champion
    4 years ago

    Is there any relationship between the two tables?

  • deevaker's avatar
    deevaker
    Resolver I
    4 years ago

    Hi PC2790 ,

     

    I think instead of making 2 rows, it is better to have them in columns because I dont see a key on second table where you will be able to filter the values.

    Thanks,

    Deevaker