cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
slaytoday17
Frequent Visitor

IF or SWITCH function in measures - Direct Query

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:

 

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

 

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. 

1 ACCEPTED SOLUTION
slaytoday17
Frequent Visitor

Found the solution

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

View solution in original post

1 REPLY 1
slaytoday17
Frequent Visitor

Found the solution

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

Helpful resources

Announcements
T-Shirt Design Challenge 2023

Power BI T-Shirt Design Challenge 2023

Submit your creative T-shirt design ideas starting March 7 through March 21, 2023.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors