Forum Discussion

New Member

8 years ago

How to filter on multiple columns

Suppose a table has FIrst Name, Last Name, Nickname. How can I setup a filter/slicer, etc, that allows users to enter a word, and return the rows with that word maching at least one of the 3 columns....

Microsoft Employee

8 years ago

Hey,

with Dax. (without knowing anything about your Data Model)

Or try custom Visual "Text Filter"

Greatz

New Member

8 years ago

Hi, thanks for your answer. It is my understanding that Text Filter searches on ONE field. If it allowed more than one it would solve my problem. Can you please be more specific on DAX?

Thanks

Anonymous
Not applicable
8 years ago
gus_sharpzen,

Create new table using DAX below, please note that there is no relationship between the new table and your original table.

Table = UNION(VALUES(Table1[First Name]),VALUES(Table1[Last Name]),VALUES(Table1[Nickname]))

Create the following measures in your original table.

Measure = FIRSTNONBLANK('Table'[First Name],1)

chekmeasure = IF( ISERROR(SEARCH([Measure],FIRSTNONBLANK(Table1[First Name],1)))=FALSE()||ISERROR(SEARCH([Measure],FIRSTNONBLANK(Table1[Last Name],1)))=FALSE()||ISERROR(SEARCH([Measure],FIRSTNONBLANK(Table1[Nickname],1)))=FALSE(),0,1)

Create slicer using field of the new table, create table visual as shown in the following screenshot, set the value of chekmeasure to 0 in visual level filters.

Regards,
Lydia
- ignas
  Advocate II
  7 years ago
  Hey all
  
  It is exactly the solution I need. I just need one more update on this solution.
  Then I deselect filter it should show a full table. Does anybody know how I can do it?
  
  Thanks in advance.
  
  Regards,
  
  Ignas
  - shanker0510
    Regular Visitor
    7 years ago
    ignas Hey Hi,
    
    Did you get this answer when you Deselect the filter ?
    
    Thanks in Advance
    Shanker