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:

 

;group1;Triage;group2;Triage;group3;

 

The formula below works :

Count = (LEN([All Involved])-LEN(SUBSTITUTE([All Involved],"Triage","")))/LEN("Triage")

 

This one is where I am getting error :

Position = FIND(";;",SUBSTITUTE([All Involved],"Triage","",[Count]))+1
 
When I try just this, it seem to work, so I am thinking that the"Data type" of [Count] is wrong :
Position = SUBSTITUTE([All Involved],"Triage","",2)
 
But, when I try this, I am now getting an error with FIND :
Position = FIND(";;",SUBSTITUTE([All Involved],"Triage","",2))+1
 
I apologize if I could not post a screenshot of the excel.
 
Thank you in advance!
 
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

2 Replies

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    6 years ago
    can you explain what error you are getting, and what you are expecting?
  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity 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