Forum Discussion
Multi-term Search Using AND Logic
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.
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.
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.
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
)
