Forum Discussion

tanehome1's avatar
tanehome1
Helper I
1 year ago

filtering data with multiple keywords parameters

I need a DAX to search multiple keywords to filter data. In my case, I would need to filter with OR operation, meaning that all rows that include any of the keywords are shown. user can write one, t...
rohit1991's avatar
rohit1991
Super User
1 year ago

Hi, tanehome1 ,

Use a calculated column or measure to filter rows based on multiple keywords entered by the user.

Solution: DAX Measure for Dynamic Filtering

SearchFilter =
VAR SearchText = SELECTEDVALUE('SearchTable'[SearchInput], "")
RETURN
IF(
    SEARCH(SearchText, 'YourTable'[YourColumn], 1, 0) > 0, 1, 0
)
  • Replace 'YourTable'[YourColumn] with the column you want to search in.
  • This works for one keyword entered by the user.a



Solution: Support Multiple Keywords

For multiple keyword searches, use multiple SEARCH conditions:

SearchFilter =
VAR Keywords = SUBSTITUTE(SELECTEDVALUE('SearchTable'[SearchInput], ""), " ", "|")
RETURN
IF(
    SUMX(
        FILTER(
            ADDCOLUMNS(
                GENERATESERIES(1, LEN(Keywords) - LEN(SUBSTITUTE(Keywords, "|", "")) + 1),
                "Word", PATHITEM(Keywords, [Value], "|")
            ),
            SEARCH([Word], 'YourTable'[YourColumn], 1, 0) > 0
        ),
        1
    ) > 0,
    1,
    0
)

 

  • This allows users to enter multiple words, separated by spaces.
  • The measure checks if any keyword exists in the column.