Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gus_sharpzen
New Member

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 9
WolfBiber
Employee
Employee

Hey,

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

Or try custom Visual "Text Filter

 

Greatz

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

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

1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 

Anonymous
Not applicable

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,

forum3.PNG

 

 

 


Filter scenarios as below,

case 1: when there is selectionforum1.PNG

 

 

 

 

case 2: when there is no selection

forum2.PNG

 

 

 


Hope it Helps 🙂

 

Regards,

Omkar

slv
Regular Visitor

@ignas did you find a solution to this?

 

Thank you

Vvelarde
Community Champion
Community Champion

@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




Lima - Peru

@Vvelarde

 

Hey Thanks it solved my issue.

 

 

 

@ignas Hey Hi,

 

Did you get this answer when you Deselect the filter ?

 

Thanks in Advance

Shanker

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.