Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
olufosh
New Member

SELECT multiple values from slicer and put it into a search function measure to filter report table

Grab multiple values from slicer and put it in a search measure to filter report table

 

I have Two Tables - No Relationship

'Azure_LastLoginDateReport' -  Table, contains Displayname, Email etc of users

'EmailTypeSearch' - Table contains a column [EmailSuffix] which holds search strings such as: @BBC.com, @CNN.com, @Sky.com, @abc.com

 

olufosh_0-1686144844524.png

 

 

I use the slicer 'EmailTypeSearch' [EmailSuffix] to select single values and filter the table report accordingly. 

 

I use a  measure "Exists" that looks at the selected Value and then filters the table report based on selected value.  - See

 

Exists = SEARCH(

    SELECTEDVALUE('EmailTypeSearch'[EmailSuffix]),

    SELECTEDVALUE('Azure_LastLoginDateReport'[Email]),

    ,-1)

 

The issue is: When trying to select multiple values in the slicer to filter the table below, it does not work. 

 

olufosh_1-1686144844527.png

 

I have seen the use of ALLSELECTED and IN and also the use of a variable, similar to the image below, but I do not know how to apply it to a Search function., as detailed above

olufosh_0-1686147922758.png

I will be extremely grateful, if you could assist in getting  me pass this stage in my development.

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

SELECTEDVALUE only works when there is only 1 value. To use multiple strings you could iterate over each one and check for a match. I don't know how well this will do performance-wise but you could try

Exists =
MAXX (
    VALUES ( 'EmailTypeSearch'[EmailSuffix] ),
    SEARCH (
        'EmailTypeSearch'[EmailSuffix],
        SELECTEDVALUE ( 'Azure_LastLoginDateReport'[Email] ),
        ,
        -1
    )
)

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

SELECTEDVALUE only works when there is only 1 value. To use multiple strings you could iterate over each one and check for a match. I don't know how well this will do performance-wise but you could try

Exists =
MAXX (
    VALUES ( 'EmailTypeSearch'[EmailSuffix] ),
    SEARCH (
        'EmailTypeSearch'[EmailSuffix],
        SELECTEDVALUE ( 'Azure_LastLoginDateReport'[Email] ),
        ,
        -1
    )
)

Hi @johnt75  - Thank you so much. That worked as expected. I am only dealing with circa 10k records, therefore it should be fine performance wise.  Many thanks. 

 

For ALL - I am also be interetsed how this script can be adapted to use a variable, but only if feasible and possible as I can see cases where this may be needed. for example where a search and sub-search is required

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors