topic Help - SUMIF where text contains a specific word in DAX Commands and Tips

Help - SUMIF where text contains a specific word

m4d — Fri, 21 Jan 2022 04:35:38 GMT

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.

Re: Help - SUMIF where text contains a specific word

Jihwan_Kim — Fri, 21 Jan 2022 04:55:09 GMT

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

Re: Help - SUMIF where text contains a specific word

VahidDM — Fri, 21 Jan 2022 05:15:23 GMT

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/

Re: Help - SUMIF where text contains a specific word

m4d — Fri, 21 Jan 2022 05:37:45 GMT

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.

Re: Help - SUMIF where text contains a specific word

Samarth_18 — Fri, 21 Jan 2022 06:20:51 GMT

Hi m4d ,

You can try any of below code:-

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

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

Thanks,

Samarth

Re: Help - SUMIF where text contains a specific word

m4d — Sun, 23 Jan 2022 23:05:26 GMT

The first one worked perfectly - thank you very much!