Forum Discussion

dnana's avatar
dnana
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago
Solved

Filter single column with multiple conditions (words in a string)

Team,

 

I would like to create a measure to filter this table based on a single column that has multiple conditions. I would like to return NAME that has the words "red" and "green" in STRING column.

 

 

The result:

 

 

Any guidance would be appreciated.

DAX FILTER
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    dnana Try:

    Selector Measure = 
  VAR __Name = MAX('Table'[NAME])
  VAR __Table = SUMMARIZE('Table',[NAME],"__STRING",CONCATENATEX('Table',[STRING]))
  VAR __Table1 = ADDCOLUMNS(__Table,"__Red",SEARCH("red",[__STRING],,0),"__Green",SEARCH("green",[__STRING],,0))
  VAR __Names = DISTINCT(SELECTCOLUMNS(FILTER(__Table1,[__Red]>0 && [__Green]>0),"__Name",[NAME]))
RETURN
  IF(__Name IN __Names,1,0)

    Put this measure in the visual filters section of your Filters pane and filter for 1.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    dnana Try:

    Selector Measure = 
  VAR __Name = MAX('Table'[NAME])
  VAR __Table = SUMMARIZE('Table',[NAME],"__STRING",CONCATENATEX('Table',[STRING]))
  VAR __Table1 = ADDCOLUMNS(__Table,"__Red",SEARCH("red",[__STRING],,0),"__Green",SEARCH("green",[__STRING],,0))
  VAR __Names = DISTINCT(SELECTCOLUMNS(FILTER(__Table1,[__Red]>0 && [__Green]>0),"__Name",[NAME]))
RETURN
  IF(__Name IN __Names,1,0)

    Put this measure in the visual filters section of your Filters pane and filter for 1.

    • dnana's avatar
      dnana
      Icon for Microsoft Employee rankMicrosoft Employee
      4 years ago

      Thank you Greg! Appreciate the quick response and helping me learn more about DAX.