cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
911Data
Helper I
Helper I

Trying to create a TRUE/FALSE Column with multiple conditions across different fields

Hello,

I'm trying to create a TRUE/FALSE column with conditions from 2 different columns. The "search" DAX function seems to work partially, but is not being as specific as I would like. 

The logic for the formula is as follows:

IF [meddislvl] contains "??O*" and [Total Time On Phone (s)Secs] is > 300, then TRUE, else FALSE

The formula I used is:

 

Omega Overage =IF(

AND(

SEARCH ( "??O*", [meddislvl], 1, 0) > 0,

[Total Time On Phone (s)Secs] > 300

),

"TRUE", "FALSE")



However this only appears to work partially, as it ignores the 2 character limit and wildcard, and returns TRUE for any and all [meddislvl] that contains an "O" in it, regardless if it has 2 characters in front, or multiple characters after. An example of what I would specifically want is it to return TRUE for [meddislvls] that contain something like "77O01M", but instead it also returns TRUE for [meddislvls] like "06E01O" which I do not want (as the main identifier for an OMEGA, is the "O" being in the center of the [meddislvls] rather than the end, and the "E" represents an "ECHO" in this example). 

I appreciate your time and assistance!

1 ACCEPTED SOLUTION
911Data
Helper I
Helper I

It seems that the SEARCH function didn't like the wildcard being in the formula, so I just used "?", added multiple searches within the formula and added an OR operator " || ", and that seems to have done the trick. Formula below:

Omega Overage = 

IF((

SEARCH ( "??O??", [meddislvl], 1, 0) ||

SEARCH ("??O???", [meddislvl], 1, 0)) && [Total Time On Phone (s)Secs] > 300,

"TRUE",

"FALSE"

)

View solution in original post

1 REPLY 1
911Data
Helper I
Helper I

It seems that the SEARCH function didn't like the wildcard being in the formula, so I just used "?", added multiple searches within the formula and added an OR operator " || ", and that seems to have done the trick. Formula below:

Omega Overage = 

IF((

SEARCH ( "??O??", [meddislvl], 1, 0) ||

SEARCH ("??O???", [meddislvl], 1, 0)) && [Total Time On Phone (s)Secs] > 300,

"TRUE",

"FALSE"

)

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors