Calculated column with IF...Else statement

[mohsenask]

Hi 

I have the following tables; only mentioning relevant columns here

Merchants with columns

CompanyID
MerchantName

AllTransactions

PaymentIntentId

CompanyID

Fees

BrandName

IS_Premium (True/False)

Fee_Type (domestic/international)

perc_amount

MerchantID

Payouts

PaymentIntentID

ChargeID

BrandID

Fee_Type (domestic/international)

Is_Premium

all relationships exists between the tables

Based on the paymentIntentID and chargeID from the Payouts table, we get the info of the Brand, Fee_type, IS_premium and Company ID for the Company and i created a visual that filters data to as below

Now i want to have a calculated column that looks at the combination of the above columns and select the perc_amount from the Fees.

Can someone please advise what can be the IF...Else statement to get a combination of the four columns and derive the perc_Amnt from the Fees table?

Thanks

@amitchandak 

[hashtag_pete · Accepted solution]

Hmm,

this gets very theoretical without actual tables, but you can include in the CONCATENATE formular the LOOKUPVALUE, such as 

"New Column" = CONCATENATE ( LOOKUPVALUE(...), ChargeID , BrandID , Fee_Type (domestic/international), Is_Premium)

not quite sure if I am loosing it here 😄

hashtag_pete

[hashtag_pete]

Hey, 

in DAX it would be "New Column" = CONCATENATE ( ChargeID , BrandID , Fee_Type (domestic/international), Is_Premium)

That would work regardless of the type integer, ...

Does that help? If so, please

• mark this post as a solution
• give kudos

Thanks hashtag_pete

[mohsenask]

@hashtag_pete 

How about concatenating columns from two tables?

[hashtag_pete]

Hello @mohsenask ,

could you do yet another calculated column where you concatenate the Merchant, Brand, Fee and and premium? If you do this on both sides, you could get the according fee with a lookupvalue.

Just an idea - hope it helps

hashtag_pete

[mohsenask]

Yes thats an idea I had but the the tables are coming in from Postgres except for

AllTransactions

PaymentIntentId

CompanyID

which comes from SQL Server

The Concatenation doesnt work giving an error - invalid input syntax for integer.

can you help with the correct syntax for concatenation?

[amitchandak]

@mohsenask , You have like

I think in payout Table

Switch( True() ,

[Fee_Type] = "international"  && related(MerchantName)  = "XXX" && related([BrandName] ) = "YYY" & [ChargeId] = "ZZZ", True() ,

// Add more conditions

false()

)

related can be used from one to Many size else use alternatives 

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[mohsenask]

Is that column ??

Also how does this connect and derive perc_amount?