Forum Discussion

gus_sharpzen's avatar
gus_sharpzen
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. Such as in where LastName=<SearchString> or FirstName=<SearchString> or Nickname=<SearchString>? 

9 Replies

    • gus_sharpzen's avatar
      gus_sharpzen
      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's avatar
        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