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

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

    • shanker0510's avatar
      shanker0510
      Regular Visitor
      7 years ago

      Vvelarde

       

      Hey Thanks it solved my issue.