Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
elponce
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

@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
Vvelarde
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

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 

@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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.