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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Returning value in cell if another cell contains specific text

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:
Test.png
1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

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 () )
)

Capture.PNG

Or we can create a calculated column as below.

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

2.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

8 REPLIES 8
v-frfei-msft
Community Support
Community Support

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 () )
)

Capture.PNG

Or we can create a calculated column as below.

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

2.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
parry2k
Super User
Super User

@Anonymous add following measure

 

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@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
Not applicable

@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)

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.