Forum Discussion
Multi-term Search Using AND Logic
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!
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.
10 Replies
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 ResultAdd this measure as a visual level filter to your table visual, set to show only when the value is 1.
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?
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.
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.
HI ScottData,
Checking in to see if your issue has been resolved. let us know if you still need any assistance.
Thank you.
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.
