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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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