Create a measure with if statetment to filter data

[elponce]

 Hello!

I have a column with text data, and I am trying to create a measure with an 'if' statement that I can use to filter that data in 2 categories. I am looking for rows that contain both words "investigation" and "perform", if this is true then "Investigation" otherwise "Action Item". I tried to use the formula below, but I do not fully understand how the function 'CONTAINS' or if there is a better way to do it.

Action Item Type = if(CONTAINS(AI_Open,AI_Open[Action Item Note],"perform",AI_Open[Action Item Note],"investigation"),"Investigation","Action Item")

Thank you in adavnce for your help,

[Vvelarde]

@elponce

CONTAINS is use to compare Columns or Tables

https://msdn.microsoft.com/en-us/library/gg492183.aspx

In your case you can use FIND 

https://msdn.microsoft.com/en-us/library/ee634882.aspx

Or Search

https://msdn.microsoft.com/en-us/library/ee634235.aspx

Regards

Victor

[elponce]

I tried using the "Search" function (see formula below), but for some reason it is not recognizing the column from the table. It is giving me the error below.

AI Type = 
IF (
    SEARCH ( "investigation", AI_Open[Action Item Note],, 0 ) > 0
        || SEARCH ( "perform", AI_Open[Action Item Note],, 0 ) > 0,
    "Investigation",
    "Action Item"
)

 

[v-yuezhe-msft · Accepted solution]

@elponce,

Do you right-click on your  table and select "New Column" to apply the above DAX, your formula works in my scenario.


Regards,

Lydia

[elponce]

Lydia--

No, I did not do that. I was creating a measure and using that formula. Now it works!

Thank you for your help!

Eric