cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sutmar Frequent Visitor
Frequent Visitor

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. 

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-haibl-msft Super Contributor
Super Contributor

Re: Show items with text field based on selected text

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

 

Show items with text field based on selected text_1.jpg

 

Best Regards,
Herbert

sutmar Frequent Visitor
Frequent Visitor

Re: Show items with text field based on selected text

@v-haibl-msft

 

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

8 REPLIES 8
Super User
Super User

Re: Show items with text field based on selected text

Hi,

 

Try this calculated column formula in Table1

 

=FIRSTNONBLANK(FILTER(VALUES(Table2[Keywords]),SEARCH(Table2[Keywords],Table1[DSF],1,0)),1)

 

Untitled.pngUntitled1.png

sutmar Frequent Visitor
Frequent Visitor

Re: Show items with text field based on selected text

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.

2017-11-19 10_34_06-Book1 - Excel.png

Super User
Super User

Re: Show items with text field based on selected text

Hi,

 

I am still not clear.  Show me your raw data, your filter criteria and your expected result.

pxg08680 Member
Member

Re: Show items with text field based on selected text

@sutmar

Going with the output directly, this works but not a suggested way.

 

Step1:

SLPIT YOUR DSF COLUMN IN TABLE1 USING COMMA DELIMITER. AND MAKE THESE NEW RELATIONSHIP BETWEEN THESE DSF SLPIT COLUMNS AND DSF_SELECT FROM TABLE2 AND JOIN THEM AS BELOW.

a2.PNG

a1.PNG

STEP 2: DROP YOUR DSF_SELECT COLUMN INTO SLICER AND SELECT ANY. SELECTING DSF1 YOU NEED TO GET POEJECTA INFO ONLY ACCORDING TO YOUR REQ.

 

a3.PNG

THIS WORKS GOOD BUT LONG PROCESS AND NOT A SUGGESTED ONE.

sutmar Frequent Visitor
Frequent Visitor

Re: Show items with text field based on selected text

@pxg08680

 

Thank you, the rsult is exactly what I need and I also thought about this split process, but as you mention, a long process to go.

v-haibl-msft Super Contributor
Super Contributor

Re: Show items with text field based on selected text

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

 

Show items with text field based on selected text_1.jpg

 

Best Regards,
Herbert

sutmar Frequent Visitor
Frequent Visitor

Re: Show items with text field based on selected text

@v-haibl-msft

 

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

sutmar Frequent Visitor
Frequent Visitor

Re: Show items with text field based on selected text

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