Measure to sum values in one column if another column does not contain certain text.

[nvizard]

I am trying to create a measure that would sum the values in one column, 'Time(Days)', only if the column, 'Task', does not contain the text "leave". 

I was thinking of something along the lines of:

Measure = Calculate(sum(Time(Days), Filter(Table 1, Task does not contain "leave")

alternatively, I could create a measure that sums along Timedays if Task contains the text leave, another measure that sums timedays then subtract the latter from the former.

Any help would be much appreciated. 

dataset

[Tara_ · Accepted solution]

Hi @nvizard ,

You can use this measure with the Search funtion

NotSumOfLeave = CALCULATE(SUM('Table'[Time (Days)]),

      FILTER('Table', NOT(SEARCH("leave", 'Table'[Task],,0))))

Here is a link with more options and explanations

https://community.powerbi.com/t5/Desktop/DAX-FIND-or-SEARCH-part-of-a-string-in-a-column/td-p/134518

Hope this helps,

Tara

[Gordonlilj]

Hi,

Perhaps something like below could work

Measure = 
CALCULATE(
  SUM('Table'[Days]),
  FILTER('Table',NOT(FIND("Leave",'Table'[Task],,0))))