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

[Anonymous]

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 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 case). 

I appreciate your time and assistance!

[Anonymous]

It seems that the SEARCH function didn't like the multiple character 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"

 )

[Greg_Deckler]

@Anonymous How about:

Omega Overage =
  IF( 
    SEARCH ( "O", [meddislvl], 1, 0) > 2 && [Total Time On Phone (s)Secs] > 300,
    "TRUE", 
    "FALSE"
  )

[Anonymous]

@Greg_Deckler Unfortunately this still returns a TRUE for non-OMEGA events.

Seems its still returning TRUE for "any [meddislvl] that has an "O" in it ANYWHERE" rather than specifically with 2 characters in front of the "O" and with multiple characters after it. 

[Greg_Deckler · Accepted solution]

@Anonymous Oh, sorry, I read the original post wrong, how about:

Omega Overage =
VAR __Search = SEARCH ( "O", [meddislvl], 1, 0)
RETURN
  IF( 
     __Search = 3 && LEN([meddislvl])>3 && [Total Time On Phone (s)Secs] > 300,
    "TRUE", 
    "FALSE"
  )

[Anonymous]

@Greg_Deckler It seems this worked! If you have the time, could you explain how the logic/DAX works in this formula?

I don't quite understand how "VAR __Search" or "RETURN" or "__SEARCH = 3" or ">3" works in this formula. 

Thank you for your assistance!

[Greg_Deckler]

@Anonymous VAR and RETURN work together. Once you use a VAR (variable) then you have to use a RETURN statement. VAR creates a variable, in this case called "__Search". You can then use this "variable" in your DAX formula. The = 3 ensures that the O is in position 3, meaning 2 characters in front of it. The >3 means that the O is not at the end of the text (the * in your original formula). Only thing this does not do is handle cases like "aaaOaa".

[Jos_Woolley]

Hi,

Can you be more specific? You say "the main identifier for an OMEGA, is the "O" being in the center of the [meddislvls]", but in one of the examples you expect to return TRUE, i.e. 77O01M, calling "O" the 'central' letter requires further definition and clarification.

Regards