Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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>?
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
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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |