Using Search with variable using multiple strings in disconnected slicers

[Anonymous]

I have a slicer which contains a list of text fields which are available in a column of the main dataset. I have used the SEARCH function in DAX to pick up all the values from the main dataset which contains the selected text(somewhat like the "LIKE" function in SQL). It works fine, when I select a single value, however, when multiple values are selected, it starts throwing an error. What I want, is like in SQL, when 2 or more values are selected from the slicer, it UNIONS the values. Below are the screenshots for reference and the DAX used.

Here, the Tool Name slicer is used to filter data from the In_Scope_And_Additional_Tools column using the created measure Exists.

DAX for "Exists" is : 

Exists = IF(SELECTEDVALUE(Query1[ToolName]) <> " ALL",
SEARCH(SELECTEDVALUE(Query1[ToolName]),SELECTEDVALUE(SF_Opportunity[In_Scope_AND_Additional_Tools]),1,-1),1)

This works well when only 1 value is selected, However, because SELECTEDVALUE function only allows 1 selection, it doesn't allows us to select 2 or more than 2. Is there a workaround for this?

[Fowmy · Accepted solution]

@Anonymous 

Try this measure to search each item in the slicer selection and return "Exist" if found else blank.

Exists = 
VAR _SELECTED = ALLSELECTED(Query1[ToolName])

RETURN
IF( 
    ISFILTERED(Query1[ToolName]),

    IF( 
        ISBLANK(
            COUNTX(
                _SELECTED,
                SEARCH(Query1[ToolName],SELECTEDVALUE(SF_Opportunity[In_Scope_AND_Additional_Tools]),1,BLANK())
            )
        ),
        BLANK(),
        "Exist"
    ),
    BLANK()
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[amitchandak]

@Anonymous , see if this can help

https://docs.microsoft.com/en-us/dax/contains-function-dax

[Anonymous]

@amitchandak : How do we pass 2 or more values? SELECTEDVALUE is only allowing me to pass 1 value at a time.