Multi-term Search Using AND Logic

[ScottData]

Has anyone figured out how to do create a multi-term text search in Power BI that uses AND rather than OR logic? I have a table with two columns: Name of Report or Dataset, and Keywords. Each report or dataset has been tagged with multiple keywords related to the products contained in the report or dataset as well as the statistics contained in the report or dataset (ex. sales, investment, coverage area). There is a unique row for each combination of name of report or dataset and keyword. I want the user to be able to search for a product AND a statistic  and have the table that shows all the unique reports or datasets + additional metadata be filtered to just the ones that are tagged with this combination of keywords. Has anyone figured out how to do this? I tried using the existing text search filters, a disconnected search term table and developing a DAX code to try and force it to search for combinations of terms but I couldnt figure out how to force the DAX code to run when the user entered the terms in the search box.  The Power Apps visual was limited to 1000 rows and the Power Automate visual didnt include the option to send back the result of the search to the Power BI dashboard. Any help would be much appreciated!

[v-saisrao-msft · Accepted solution]

Hi @ScottData,

If the values in both columns are standardized and you want a single slicer to filter both, you can create a new combined column and use it in slicer.

Thank you.

[v-saisrao-msft]

HI @ScottData,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[v-saisrao-msft]

Hi @ScottData,

Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.

Thank you.

[johnt75]

You could create two copies of the keywords, one for each slicer, e.g.

Statistic Slicer = DISTINCT( 'Table'[Keyword] )

Product Slicer = DISTINCT( 'Table'[Keyword] )

Do not connect these new tables to the rest of the model, but create new text slicers based on each of them.

Create a new measure like

Report is visible =
VAR ReportsMatchingProduct =
    CALCULATETABLE (
        VALUES ( 'Table'[Report or dataset] ),
        TREATAS ( VALUES ( 'Product Slicer'[Keyword] ), 'Table'[Keyword] )
    )
VAR ReportsMatchingStatistic =
    CALCULATETABLE (
        VALUES ( 'Table'[Report or dataset] ),
        TREATAS ( VALUES ( 'Statistic Slicer'[Keyword] ), 'Table'[Keyword] )
    )
VAR ReportsMatchingBoth =
    INTERSECT ( ReportsMatchingProduct, ReportsMatchingStatistic )
VAR Result =
    IF ( SELECTEDVALUE ( 'Table'[Report or dataset] ) IN ReportsMatchingBoth, 1 )
RETURN
    Result

Add this measure as a visual level filter to your table visual, set to show only when the value is 1.

[ScottData]

Does this mean there would be two text search boxes? I'd prefer if there were one search box where a user could enter multiple terms. Also, the search may not be limited to a single product AND statistic, it could include multiple products and/or multiple statistics. Does this solution still work for this approach?

[johnt75]

You could do it with one slicer, just replace the different slicer table names in the code with the name of the single table you create.

Both approaches would handle multiple selections.

[ScottData]

Still need some help.

1. When i use a list slicer with search enabled, the visual tries to display every keyword. The issue is there is at least a couple hundred unique keywords. In addition, I've also listed every Name of Report or Dataset as a keyword to enable a single text search where the user can search by keyword or by the Name of Report or Dataset. 

2. The approach doesn't allow for fuzzy matching, which is key for the search feature. 

3. Even when I ran the following code including the appropriate names for my columns, when i picked a keyword from the list in the list slicer, nothing in the table changed. I believe the issue is that disconnected search terms table doesn't naturally propagate filter context to the main data table. 

Search Match_Community =
VAR SelectedKeywords =
VALUES('SearchKeywords'[Keyword])

VAR DatasetKeywords =
CALCULATETABLE(
VALUES('All Keywords'[Keyword]),
TREATAS(SelectedKeywords, 'All Keywords'[Keyword]),
'All Keywords'[Name of Report or Dataset] = MAX('All Datasets'[Name of Report or Dataset])
)

RETURN
IF(
COUNTROWS(SelectedKeywords) = 0 // show all if nothing selected
|| COUNTROWS(DatasetKeywords) = 0 // show all if no matches exist
|| COUNTROWS(SelectedKeywords) = COUNTROWS(DatasetKeywords), // AND logic
1,
0
)

[v-saisrao-msft]

Hi @ScottData,

Thank you @johnt75, for your insights.
You can perform a multi-term search in Power BI by using a disconnected search table along with the TREATAS function to apply your selected terms to the main dataset. For fuzzy or approximate matching, you can preprocess your data using Power Query’s fuzzy matching features before applying TREATAS. This approach allows for multi-term, fuzzy searches, as slicers alone do not enable filters to flow from disconnected tables.

How fuzzy matching works in Power Query - Power Query | Microsoft Learn

Create a fuzzy match (Power Query) - Microsoft Support

TREATAS function - DAX | Microsoft Learn

Model relationships in Power BI Desktop - Power BI | Microsoft Learn

Thank you.

[ScottData]

Hello, 

As you can see in my previous answer I created a disconnected search table and used TREATAS in my DAX query as had been previously suggested. This solution does not work. Nothing happens to the table visual when i enter search terms in the list or text search slicer. I have tried several variations of the code and none of them worked. 

[v-saisrao-msft]

HI @ScottData,

Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.

Thank you.