cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sutmar
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
v-haibl-msft
Microsoft
Microsoft

@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

View solution in original post

@v-haibl-msft

 

Fantastic! Thank you so much, that is it! 🙂 Very much appreciated!

View solution in original post

8 REPLIES 8
v-haibl-msft
Microsoft
Microsoft

@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

View solution in original post

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

@v-haibl-msft

 

Fantastic! Thank you so much, that is it! 🙂 Very much appreciated!

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

 

Try this calculated column formula in Table1

 

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

 

Untitled.pngUntitled1.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

@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

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

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors