cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

PowerBI Sample.JPG

 

Thank you in adavnce for your help,

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@elponce,

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

Regards,

Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Community Champion
Community Champion

@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




Lima - Peru
Highlighted

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

 

 

AI.JPGSearch Function.JPG 

Highlighted

@elponce,

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

Regards,

Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors