Forum Discussion
Sum two columns if third column contains text
H there,
I want to do a simple calculation which seems to be pretty easy in Excel but i dont get the solutions in PBI.
I have column A which has names in it.
The value in Column B and C should be added in Column D, if the text column A contains "ing" or "fy". Otherwise only the value in column B shoud be taken.
Thats an example how my data an column D should look like
A B C D
testing 1 1 2
testfy 1 1 2
test 1 3 1
testfying 1 2 3
Thanks
Carina
Hi CesiCash,
Based on my test, you could refer to below steps:
Sample data:
Create the calculated column:
Column = var a=IF(IFERROR(SEARCH("ing",[A]),BLANK())<>BLANK(),TRUE(),FALSE())||IF(IFERROR(SEARCH("fy",[A]),BLANK())<>BLANK(),TRUE(),FALSE()) return IF(a=TRUE(),[B]+[C],[B])Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
5 Replies
Hi,
Try this function in Caluclated Column
if Text.Contains([A], "ing") or Text.Contains([A], "fy") then [B]+[C] else [B]
Hi abbasabdulla
Unfortunately there is no text.contains function in powerbi?
Is there any other idea?
Best
Carina
- Hi, I don't know which powerbi products do you test my function in, I have tested it using Power BI Desktop Version: 2.41.4581.361 64-bit (November, 2016) Check the below Pibx file I worked on. https://drive.google.com/open?id=12RIcR-QVFB1waMO2w56n4OD9iq8byHcD
v-danhe-msftMicrosoft Employee
Hi CesiCash,
Based on my test, you could refer to below steps:
Sample data:
Create the calculated column:
Column = var a=IF(IFERROR(SEARCH("ing",[A]),BLANK())<>BLANK(),TRUE(),FALSE())||IF(IFERROR(SEARCH("fy",[A]),BLANK())<>BLANK(),TRUE(),FALSE()) return IF(a=TRUE(),[B]+[C],[B])Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
- v-danhe-msft
Hi CesiCash,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He
