Returning value in cell if another cell contains specific text

[Anonymous]

I want to return the value of column [Page Views] when the value of column [Page Name] contains "faqs".

If [Page Name] does not contain faqs anywhere in it, then 0 can be returned. 

I have tried three calculated columns, none of which are working:

FAQ = CALCULATE(
    SUM('Adobe Analytics'[Page Views]),
    FILTER('Adobe Analytics',FIND("Faqs",'Adobe Analytics'[Page Journey],,0)<>0))

The above measure returns the total for the entire dataset, not the row value.  

FAQ2 = IF(ISERROR(SEARCH("faqs",'Adobe Analytics'[Page Name])),[Page Views],0)

The above measure returns 0 in all cases even when [Page Name] contains faqs

FAQ3 = if(CONTAINS('Adobe Analytics','Adobe Analytics'[Page Name],"faqs"),SUM('Adobe Analytics'[Page Views]),0)

The above measure returns 0 in all cases even when [Page Name] contains faqs

I've attached a screenshot:

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

One sample for your reference. We can create a measure as below.

Measure = 
CALCULATE (
    SUM ( Table1[page view] ),
    FILTER ( Table1, SEARCH ( "faq", Table1[pagename], 1, BLANK () ) <> BLANK () )
)

Or we can create a calculated column as below.

faq = IF(ISBLANK(SEARCH("faq",Table1[pagename],1,BLANK())),BLANK(),Table1[page view])

Pbix as attached.

[parry2k]

@Anonymous add following measure

Total FAQ =
CALCULATE( COUNTROWS( Yourtable ),
CONTAINSSTRING( YourTable[YourColumn], "faq" )
)

[Anonymous]

@parry2k but [Page Views] can contain a higher value than 1 so I need to return the value in that cell... 

Also, I don't have the option of using CONTAINSSTRING, only CONTAINS and CONTAINSROW - both of which return #ERROR

[Anonymous]

@parry2k I've just played around with your formula and discovered that the below works - but only if you use the entire contents of [Page Name] - I need it to search for part of the string not an absolute value...

FAQ = IF(CONTAINS('Adobe Analytics','Adobe Analytics'[Page Name],"int:members:questions:faqs-pre-trip-planning"),[Page Views],0)

[parry2k]

@Anonymous i think you don't have latest powerbi version and that's why containsstring is not working for you. upgrade your desktop and it should work.

[Anonymous]

@parry2k  sadly I am contstrained by updates available in my company. I am using 2.64.5285.741 64-bit (November 2018). 

Ah well I guess there isn't a solution for me on this occasion 😞 

Thanks anyway

[parry2k]

@Anonymousthere is always a solution, do me a favour, share sample data in excel from onedrive/google drive.

[Anonymous]

I managed to work out a solution in Query Editor rather than DAX using if Text.Contains()

I would rather have done it as a calculated column in DAX, than mess with my dataset, but it works so i'm happy!