Multiple IF ELSE across two tables

[mohsenask]

Hi all,

I have two tables - M_fees which as below and another table of transactions (All_Fees) for the company with multiple fields

I want to create a calculated column in the All_fees transaction table which will check each record for the following conditions;

i.e. Fees_applicable = if All_Fees[Brand_ID] and All_Fees[Fee_Type] and All_fees[Is_Premium] and All_Fees[company name]
THEN M_fees[Perc_Amt] or M_fees[Value]

i dont seem to understand how to use IF ELSE statements across two tables.

Please help

@amitchandak 

 

[v-angzheng-msft · Accepted solution]

Hi, @mohsenask 

You can use the LOOKUPVALUE function to help you.

I have to say that the company names in your tables are not the same, which makes me keep my formula continuously failing.

And the case of the fields in the table is best to be the same, you can transform the case of the field in PowerQuery.

Okay, what we have to do next is to create a calculated column as follows:

Column = 
VAR _A=
LOOKUPVALUE(
    M_fees[PERC_AMT],
    M_fees[COMPANY NAME],All_Fees[MetadataCompany],
    M_fees[BRAND_ID],All_Fees[card_ brand],
    // M_fees[IS_Premium],All_Fees[IS._Premeium]
    M_fees[IS_ PREMIUM],All_Fees[C. _Premium]
   )
VAR _B="IF A = BLANK THEN B ELSE A"
RETURN _A
// The value of VALUE column in M_fees table is blank, if you want just blankvalue, you don't need the VAR _B

Result:

Since the data above doesn't have a value that can be matched so I added four extra rows.

And in your table, if you want to do it with a measure, it would be helpful if the Boolean value can be converted to 0 and 1. You can change your source data, or add a calculated column to display the Boolean value as a number.

The calculated column looks like this:

IS._Premeium = [C. _Premium]+0

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[Fowmy]

@mohsenask 

Your question is not clear, can you share the expected output and mention if your tables are connected:

An example of IF:

Price Group =
IF(
    'Product'[List Price] < 500,
    "Low",
    IF(
        'Product'[List Price] < 1500,
        "Medium",
        "High"
    )
)

[mohsenask]

@Fowmy Thanks for the quick reply

Im creating a new column in the below visual where I want to check the multiple column values in the below table will bring me the value.......

.........of the Perc_Amt from the below highlighted table;