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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors