Forum Discussion

steambucky's avatar
steambucky
Helper III
7 years ago
Solved

Issues with AND

 
Here is some code that works. Is searchs the DESCRIPTION field for the words "coal", "statistics". If it finds a those words, it adds 1 to the coal stat score. 
 
Coal Stat score =
var _description_coal=IF ( ISBLANK ( SEARCH ( "coal", 'SPOC_1-1-2018_to_7-2-2019'[COMMENTS], 1, BLANK () ) ),
0,1)
var _description_statistics=IF ( ISBLANK ( SEARCH ( "statistics", 'SPOC_1-1-2018_to_7-2-2019'[DESCRIPTION], 1, BLANK () ) ),
0,1)
Return (_description_coal+_description_statistics)
 ---
 
What I want to do is find feilds that contain both using AND. I know this doesnt work...but I am trying...
 
 Coal Stat score =
var _description_coal=(
IF ( ISBLANK ( SEARCH ( "coal", 'SPOC_1-1-2018_to_7-2-2019'[COMMENTS], 1, BLANK () ) )
&&
var _description_statistics=IF ( ISBLANK ( SEARCH ( "statistics", 'SPOC_1-1-2018_to_7-2-2019'[DESCRIPTION], 1, BLANK () ) )
),
0,1)
Return 
 
If I found both words my expected output would be 1. 
 
Any help is welcome.
 
 
 
 
 
 
 

Need Help
  • AlB's avatar
    AlB
    7 years ago

    Hi steambucky

    I'm not quite sure I understand what you need as I believe you have all the info you need already in the code you show.

    Let

    _description_all = _description_coal + _description_statistics 

    then

    If   _description_all = 0  ->  No description found

    If   _description_all = 2  ->  Both coal and statistics found

    If   _description_all = 1 ->   If  _description_coal =1  only "coal" found , otherwise only "statistics" found

     

    Should you want to do everything in one go and return 1 only when both are found:

     

    Coal Stat score =
IF (
    SEARCH ( "coal", 'SPOC_1-1-2018_to_7-2-2019'[COMMENTS], 1, 0 ) <> 0
        && SEARCH ( "statistics", 'SPOC_1-1-2018_to_7-2-2019'[DESCRIPTION], 1, 0 ) <> 0,
    1,
    0
)

     

     

1 Reply

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi steambucky

    I'm not quite sure I understand what you need as I believe you have all the info you need already in the code you show.

    Let

    _description_all = _description_coal + _description_statistics 

    then

    If   _description_all = 0  ->  No description found

    If   _description_all = 2  ->  Both coal and statistics found

    If   _description_all = 1 ->   If  _description_coal =1  only "coal" found , otherwise only "statistics" found

     

    Should you want to do everything in one go and return 1 only when both are found:

     

    Coal Stat score =
IF (
    SEARCH ( "coal", 'SPOC_1-1-2018_to_7-2-2019'[COMMENTS], 1, 0 ) <> 0
        && SEARCH ( "statistics", 'SPOC_1-1-2018_to_7-2-2019'[DESCRIPTION], 1, 0 ) <> 0,
    1,
    0
)