Show items with text field based on selected text

I have a list of Projects in a table [Projects] with a column [DSF] containing text. I created another table [Select] with a Column listing words that shall be used as a filter. I would like to diaply all Projects that contain selected word in column [DSF]. Until now I have failed to achieve this and would appreciate suggestions.

v-haibl-msft
8 years ago
sutmar

You can create two measures with following DAX formula, one to return the selected text, another one to mark the filtered rows.

For details, please refer to attached PBIX file.

SelectedString = IF ( ISFILTERED ( Table2[DSF_Select] ), SELECTEDVALUE ( Table2[DSF_Select], CONCATENATEX ( Table2, Table2[DSF_Select], ", " ) ), "Not Select" )

Mark = SEARCH ( [SelectedString], CALCULATE ( MAX ( Table1[DSF] ) ), 1, -1 )

Drag the Mark measure to visual level filter of the Table visual as following screenshot, set it greater than 0.

Best Regards,
Herbert
Show items with text field based on selected text.pbix34 KB
sutmar
8 years ago
v-haibl-msft

Fantastic! Thank you so much, that is it! :-) Very much appreciated!

8 Replies

v-haibl-msft
Microsoft Employee
8 years ago
sutmar

You can create two measures with following DAX formula, one to return the selected text, another one to mark the filtered rows.

For details, please refer to attached PBIX file.

SelectedString = IF ( ISFILTERED ( Table2[DSF_Select] ), SELECTEDVALUE ( Table2[DSF_Select], CONCATENATEX ( Table2, Table2[DSF_Select], ", " ) ), "Not Select" )

Mark = SEARCH ( [SelectedString], CALCULATE ( MAX ( Table1[DSF] ) ), 1, -1 )

Drag the Mark measure to visual level filter of the Table visual as following screenshot, set it greater than 0.

Best Regards,
Herbert
Show items with text field based on selected text.pbix34 KB
- sutmar
  Frequent Visitor
  8 years ago
  v-haibl-msft
  
  Fantastic! Thank you so much, that is it! :-) Very much appreciated!
- sutmar
  Frequent Visitor
  8 years ago
  v-haibl-msft
  
  Could the setting be adapted that I see all the projects if no value is selected? And would it possible to allow multiple filter values?
Ashish_Mathur
Super User
8 years ago
Hi,

Try this calculated column formula in Table1

=FIRSTNONBLANK(FILTER(VALUES(Table2[Keywords]),SEARCH(Table2[Keywords],Table1[DSF],1,0)),1)
- sutmar
  Frequent Visitor
  8 years ago
  Thank you very much, but it is not what I am trying to achieve. Please see picture to visualize what I try to do with BI Desktop.
  Table one should only display those items containing the words in DSF that I have selected in DSF_Select Slicer.
  - Ashish_Mathur
    Super User
    8 years ago
    Hi,
    
    I am still not clear. Show me your raw data, your filter criteria and your expected result.