Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.