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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.