Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Search is not behaving

I could have the simplest of solutions if I could get the SEARCH function to work (as it does in a slicer-search or with the Text Search Visual, in a calculated column or measure. My scenario is that I need to look at multiple columns based off of a single slicer-selection. I need the slicer text to look across the row at both manager columns and tell me if it finds the text. Can't get this one to work.. any thoughts?

 

Thank you

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    You can try this measure expression to return a 1 when one of the manager columns matches your slicer value.  I assume your slicer values are in a disconnected table.  Replace Slicer and Table with your actual table names.

     

     

    IsMatch =
VAR slicer =
    SELECTEDVALUE ( Slicer[Manager] )
RETURN
    IF (
        NOT (
            ISBLANK (
                COUNTROWS (
                    FILTER (
                        ALLSELECTED ( Table ),
                        Table[Manager1] = slicer
                            || Table[Manager2] = slicer
                    )
                )
            )
        ),
        1
    )

     

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Anonymous ,

    Search() function is used to return the number of the starting position of the first text string from the first character of the second text string. It will not be applied in this issue.

    You can try to create a measure like this:

    Result = 
var _managers = SELECTEDVALUE(Managers[Managers])
var _manager1 = SELECTEDVALUE('Table'[manager1])
var _manager2 = SELECTEDVALUE('Table'[manager2])
return
IF(
    HASONEFILTER(Managers[Managers]),
    IF(
        _managers = _manager1 || _managers = _manager2,
        "yes",
        "no"
    ),
    "error"
)

    before slicerafter slicer

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    You can try this measure expression to return a 1 when one of the manager columns matches your slicer value.  I assume your slicer values are in a disconnected table.  Replace Slicer and Table with your actual table names.

     

     

    IsMatch =
VAR slicer =
    SELECTEDVALUE ( Slicer[Manager] )
RETURN
    IF (
        NOT (
            ISBLANK (
                COUNTROWS (
                    FILTER (
                        ALLSELECTED ( Table ),
                        Table[Manager1] = slicer
                            || Table[Manager2] = slicer
                    )
                )
            )
        ),
        1
    )

     

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you for the suggestion. I'll give this a try and get back to this post with results. 
      mahoneypat v-yingjl 

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

    Search() function is used to return the number of the starting position of the first text string from the first character of the second text string. It will not be applied in this issue.

    You can try to create a measure like this:

    Result = 
var _managers = SELECTEDVALUE(Managers[Managers])
var _manager1 = SELECTEDVALUE('Table'[manager1])
var _manager2 = SELECTEDVALUE('Table'[manager2])
return
IF(
    HASONEFILTER(Managers[Managers]),
    IF(
        _managers = _manager1 || _managers = _manager2,
        "yes",
        "no"
    ),
    "error"
)

    before slicerafter slicer

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

    If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly. Thanks!


    Best Regards,
    Yingjie Li