Forum Discussion
Calculated column with IF...Else statement
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
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
7 Replies
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
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=czNHt7UXIe8Is that column ??
Also how does this connect and derive perc_amount?
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
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?
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
