Search for a list of names in a field and if found, bring it from another field if not.

[vqps8866]

Hello everyone

I need some help, because I know how to do it in Excel, but not in Power BI yet.

I have a field called SITE NAME that some tickets come with no data, but the DESCRIPTION field has this information.

So I created a formula that looks in the SITE NAME field and if it is empty it goes to the list with 3 names of sites and if it finds it it brings the name, if the field already has the name of the SITE, it brings the name that is in the field.

Here is the formula I use in Excel: =IF([@[Site Name1]]="",INDEX(LIST,MATCH(1,COUNTIF([@Description],"*"&LIST&"*"),0)),[@[Site Name1]])

How could I do something like this in Power BI.

Thanks in advance for your help.

[xifeng_L]

Hi @vqps8866 

I'm not sure if I misunderstood your needs, you can refer to below calculated column.

SiteName Update = 
VAR List = {"SiteA","SiteB","and more..."}
RETURN
IF('Table'[Site Name]=BLANK(),
    CONCATENATEX(
        FILTER(List,FIND([Value],'Table'[Description],1,0)),
        [Value],
        ","
    ),
    'Table'[Site Name]
)

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[vqps8866]

Hello

How are you?

I will test it and I will update you.

Many tks for your time and support.

[Anonymous]

Hi, @vqps8866 

Looking forward to your reply, if the reply is helpful to you, I hope you will accept it as a solution.

If you find a solution, you are also welcome to share it to find a solution faster for members of the community who have similar problems to you.

 
Of course, if you have any new ideas, you are welcome to contact us.
 

Best Regards,

Leroy Lu

[vqps8866]

Hello Leroy Lu

I used an alternative solution to make it work and as soon as I can I will test the opinion and then accept it as a solution.

Thank you very much in advance