Forum Discussion
Create a measure with if statetment to filter data
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,
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
4 Replies
- Vvelarde
Community Champion
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
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" )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
