cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors