Show items with text field based on selected text

[sutmar]

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 · Accepted solution]

@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

[v-haibl-msft · Accepted solution]

@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

[sutmar]

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

[sutmar · Accepted solution]

@v-haibl-msft

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

[Ashish_Mathur]

Hi,

Try this calculated column formula in Table1

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

[sutmar]

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.

[pxg08680]

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

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.

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

[sutmar]

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

[Ashish_Mathur]

Hi,

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