Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

FIND and SUBSTITUTE

Good day Everyone,   I hope someone can shed a light to my issue as I am stuck. So I wanted to replicate a formula from Excel to PowerBI   the column for  [All Involved] looks like this:   ;gro...
Need Help
Tips and Tricks
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  Anonymous 

    There are two mistake in your Position formula

    1. 

    SUBSTITUTE(<text>, <old_text>, <new_text>, <instance_num>)

    For <instance_num>, it could not be 0, so you need to add a IF conditional in the formula

    https://docs.microsoft.com/en-us/dax/substitute-function-dax

    2.

    FIND(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])

    For FIND function, it will return the starting position of one text string within another text string, but if there is no result and you need to define a <NotFoundValue>

    https://docs.microsoft.com/en-us/dax/find-function-dax

     

     

    So adjust the formula as this

    Position = IF([Count]>0, FIND(";;",SUBSTITUTE([All Involved],"Triage","",[Count]),1,0)+1)

     

    Regards,

    Lin

v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

hi  Anonymous 

There are two mistake in your Position formula

1. 

SUBSTITUTE(<text>, <old_text>, <new_text>, <instance_num>)

For <instance_num>, it could not be 0, so you need to add a IF conditional in the formula

https://docs.microsoft.com/en-us/dax/substitute-function-dax

2.

FIND(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])

For FIND function, it will return the starting position of one text string within another text string, but if there is no result and you need to define a <NotFoundValue>

https://docs.microsoft.com/en-us/dax/find-function-dax

 

 

So adjust the formula as this

Position = IF([Count]>0, FIND(";;",SUBSTITUTE([All Involved],"Triage","",[Count]),1,0)+1)

 

Regards,

Lin