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....
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

  • ignas's avatar
    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's avatar
      shanker0510
      Regular Visitor
      7 years ago

      ignas Hey Hi,

       

      Did you get this answer when you Deselect the filter ?

       

      Thanks in Advance

      Shanker

    • slv's avatar
      slv
      Regular Visitor
      7 years ago

      ignas did you find a solution to this?

       

      Thank you

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        7 years ago

        slv

         

        Hi, a different way:

         

        A new table  - ( Modeling - New Table):

         

        Opciones =
DISTINCT (
    UNION (
        VALUES ( Tabla1[FirstName] ),
        VALUES ( Tabla1[LastName] ),
        VALUES ( Tabla1[NickName] )
    )
)

        A Measure:

         

        Filtro =
VAR Word =
    SELECTEDVALUE ( Opciones[Options] )
RETURN
    IF (
        HASONEFILTER ( Opciones[Options] ),
        IF (
            CALCULATE ( COUNT ( Tabla1[FirstName] ); Tabla1[FirstName] = Word )
                + CALCULATE ( COUNT ( Tabla1[LastName] ), Tabla1[LastName] = Word )
                + CALCULATE ( COUNT ( Tabla1[NickName] ), Tabla1[NickName] = Word )
                > 0,
            1,
            BLANK ()
        ),
        1
    )

        Use this measure in the visual level filter --Is not Blank.

         

        Regards

         

        Victor

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi ignas, shanker0510

       

      Here's the complete solution,

      Create a new calculated table using DAX,

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


      Create two measures,

       

      Measure = FIRSTNONBLANK('Table'[First Name],1)
      Checkmeasure = 
var chk = 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)
return IF(ISFILTERED('Table'[First Name]),chk,2)

      Change the visual level filter as shown below,

       

       

       


      Filter scenarios as below,

      case 1: when there is selection

       

       

       

       

      case 2: when there is no selection

       

       

       


      Hope it Helps :)

       

      Regards,

      Omkar