I have a column of data that includes for example "1 person" or "10/1". I want to remove the characters leaving only the number and if there is a / I want to add the number to the left to the number to the right (e.g. 10/1 = 11).
In excel I would write the formula this way:
=IF(ProdRouteTrans[CATEGORYID]="","",
VALUE(TRIM(IF(ISERROR(FIND("/",ProdRouteTrans[CATEGORYID])),
LEFT(ProdRouteTrans[CATEGORYID],2),
LEFT(ProdRouteTrans[CATEGORYID],
FIND("/",ProdRouteTrans[CATEGORYID])-1))))
+ IF(ProdRouteTrans[CATEGORYID]="","",
VALUE(TRIM(IF(ISERROR(FIND("/",ProdRouteTrans[CATEGORYID])),0,MID(ProdRouteTrans[CATEGORYID],
FIND("/",ProdRouteTrans[CATEGORYID])+1,2))))))
But when I write the dax in pbi I get an error message "Expressions that yield variant data-type cannot be used to define calculated columns." Can someone help me fix this formula? Thank you!
