topic Need to create a TRUE/FALSE Column with multiple conditions across different fields in DAX Commands and Tips

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

Anonymous — Wed, 04 Aug 2021 13:40:05 GMT

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!

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

Greg_Deckler — Wed, 04 Aug 2021 14:10:21 GMT

Anonymous How about:

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

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

Anonymous — Wed, 04 Aug 2021 14:30:23 GMT

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.

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

Jos_Woolley — Wed, 04 Aug 2021 15:01:48 GMT

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

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

Anonymous — Wed, 04 Aug 2021 16:12:34 GMT

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"

 )

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

Greg_Deckler — Wed, 04 Aug 2021 16:42:39 GMT

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

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

Anonymous — Wed, 04 Aug 2021 17:28:37 GMT

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!

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

Greg_Deckler — Thu, 05 Aug 2021 12:13:57 GMT

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