IF or SWITCH function in measures - Direct Query

[Anonymous]

Hi everyone,

I am having some trouble with implementing a measure. 

Essentially, I want PowerBI to see if there is a certain string in each link, and if so, return a value. 

For example, using the sample below:

Table: Website

Link

/penguins/123123

/koala/345333

/koala/345333

/penguins/123123

/gorilla/500222

/gorilla/500222

/fish/988933

If a URL contains 

Penguins = PEN

Koala = KOA

Gorilla = GOR

FISH = FIS

I want the output to return:

Link	Type of website
/penguins/123123	PEN
/koala/345333	KOA
/koala/345333	KOA
/penguins/123123	PEN
/gorilla/500222	GOR
/gorilla/500222	GOR
/fish/988933	FIS

I know you can use the SWITCH function, or the IF Function with CONTAINSSTRING if I was to create a column (which I can't because we use direct query). I also know to get the ^ above output, I need to add some row context, but I can't identify an iterator function that would be appropriate.

Is there a solution here? or should I rebuild by using IMPORT and then add columns. 

Thank you very much! Would appreciate any clear instructions as I'm a PowerBI newbie. 

[Anonymous · Accepted solution]

Found the solution

Agent =
VAR selectedrow =
SELECTEDVALUE('website'[link])
RETURN
 SWITCH (
    TRUE (),
    ISERROR ( FIND ( "penguins", selectedrow ) ) <> TRUE (), "PEN",
    ISERROR ( FIND ( "gorilla",  selectedrow ) ) <> TRUE (), "GOR",
)