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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors