smartfilterbySQLBi

[Oketa_Emma]

Hellos

I am requesting for your assistance, I am using the SmartFilterBySQLBI in my desktop power Bi reporting Vitalization to help me search for names of members that participated in an event. I have a column, ("Team") with names of a Team of two members that participated in an event. E.g. Oketa Emma and Tutu Moses in many rows. But a member like Tutu Moses can also be with many other different members starting or ending with his name, e.g. Tutu Moses and Onen Geofrey..... Etc. I also created a column of unique names ("Names") of all the participants e.g. Tutu Moses....etc. in rows with a created relationship to the Team, Which i used in the SmartFilterBySQLBI visualization field. 

When I search and select Tutu Moses from the SmartFilterBySQLBI search box, I am only able to view results of Tutu Moses only when the Team starts with Tutu Moses but not When it ends with Tutu yet I am interested in all both starting and ending.

QN How can i make the SmartFilterBySQLBI search box display results of all the teams containing Tutu Moses?

Thank you

Oketa

[v-ljerr-msft · Accepted solution]

Hi @Oketa_Emma,

If I understand you correctly, you should be able to follow steps below to get your expected result.

I assume you have a table with "Team" column called "Table1", and a table with "Name" column called "Table2" like below.

1. Remove the relationship between "Table1" and "Table2" if there is.

2. User the formula below to create a new measure.

Measure =
SUMX (
    Table2,
    FIND (
        FIRSTNONBLANK ( Table2[Name], 1 ),
        FIRSTNONBLANK ( Table1[Team], 1 ),
        ,
        0
    )
)

3. Use the measure as visual level filter on the visual that you want to display the "Team" column, and apply "is greater than 0" for the filter.

4. Then you should be able to use the "Name" column in the SmartFilterBySQLBI visualization field to display results of all the teams containing Tutu Moses etc.

Here is the sample pbix file for your reference. 

Regards

[Oketa_Emma]

Thanks very much, it worked!!!