Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mohsenask
Helper II
Helper II

Multiple IF ELSE across two tables

Hi all,

 

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

 

Screenshot 2021-11-07 at 17.21.01.png

 

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 

 

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

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.

vangzhengmsft_0-1636512144951.png

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.

vangzhengmsft_1-1636512351517.png

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:

vangzhengmsft_2-1636512449542.png

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.

View solution in original post

3 REPLIES 3
v-angzheng-msft
Community Support
Community Support

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.

vangzhengmsft_0-1636512144951.png

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.

vangzhengmsft_1-1636512351517.png

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:

vangzhengmsft_2-1636512449542.png

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
Super User
Super User

@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"
    )
)

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@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.......

mohsenask_0-1636293441375.png

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

 

Screenshot 2021-11-07 at 17.21.01.png

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors