Help - SUMIF where text contains a specific word

[m4d]

Hi everyone, 

I've been researching this but I can't seem to find a solution. 

My dataset is like the below:

Activity Type       Hours     

Initial Tutorial       4

Initial Lecture       5

Repeat Tutorial     6

I want to create a new column that returns the hour value if the Activity Type column contains "Tutorial". If the row does not contain "tutorial" I want it to return a blank cell. I want this to evaluate row by row. There are many versions of the Activity Type that will be added on to this dataset and I can't create an exhaustive list. 

I don't use DAX often so feeling a bit stuck. Any help is greatly appreciated. 

[Samarth_18 · Accepted solution]

Hi @m4d ,

You can try any of below code:-

Column =
IF (
    SEARCH ( "Tutorial", 'Table'[Activity Type], 1, 0 ),
    'Table'[Hours],
    BLANK ()
)

or

Column = 
IF (
    Find ( "Tutorial", 'Table'[Activity Type], 1, 0 ),
    'Table'[Hours],
    BLANK ()
)

Thanks,

Samarth

[m4d]

The first one worked perfectly - thank you very much!

[VahidDM]

Hi @m4d 

use this code to add a new column with DAX:

New Column =
IF (
    CONTAINSSTRING ( 'Table'[Activity Type], "Tutorial" ),
    'Table'[Hours],
    BLANK ()
)

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[m4d]

Thanks, unfortunately power pivot won't recognise CONTAINSSTRING. Is there a workaround or is it not possible in Excel? I have the latest 365 version.

[Jihwan_Kim]

Hi,

please check the below. It is for creating a calculated column ( not a calculated measure).

Hours tutorial CC =

IF ( CONTAINSSTRING ( Data[Activity Type], "Tutorial" ), Data[Hours] )