Filtering a column with delimiter

[aa_KF]

Hello, I have a basic table (T1) as shown on the left, I want to be able to filter the rows based on the three available values: A, B, and C. For example if I choose "C" from the filter the table will only show rows with ID 1,2,3 only. 

I have imported the table(T2) and created another table that contain all the possible valuse:  (I used Col2 as the value of the filter)

I then duplicated T1 in order to seperate the values into seperate rows as shown in T3:

I want to show the table without duplicate rows as in T3 but I want to be able to link it somehow with T1 and T2, and then use the filter. 

Appreciate your help and time!

[Anonymous]

@aa_KF  - 

You can create a new Measure like this:

Filter Found Measure = 
var text_to_find = SELECTEDVALUE('YourFilterTable'[FilterColumn])
var text_to_search = MAX('YourDataTable'[DataColumn])
return FIND(text_to_find,text_to_search,1,BLANK())

Then add this measure to your table. Those values that do not contain the search text will result in BLANK. Rows with only blank measures are, by default, excluded from the result set. You can make the column very narrow so that it won't be displayed.

Hope this helps,

Nathan

[Anonymous]

@Anonymous ,

That was a good idea.

I would like to suggest creating measure which returns the original string instead of positions like this.

Filter Found Measure =

var text_to_find = SELECTEDVALUE(Table_to_be_searched[Column_to_be_searched])

var text_to_search = MAX('Search_table'[Search_column])

var FIND_RES = FIND(text_to_find,text_to_search,1,BLANK())

return IF(FIND_RES >= 1, text_to_search)

And you can direclty refer to measure in visual.

Hope this hepls,

Thanks.

[aa_KF]

Thank you @Anonymous  for your help I appreicate it, would you please elaborate more?

I understand that I won't need the third table (where I split up the table values into seperate columns). and by adding new measure to my basic table I'll be able to use as the value filter?

Thank you!

[Anonymous]

@aa_KF 

That's correct -

You don't need the third table.

You can have one table that will contain your different filtering possibilities and another table with the text that you want to analyze. It doesn't matter where you create the measure, in terms of functionality, but it would make the most intuitive sense to include it in the table that contains the text like "ABC".

Then, add the new measure to the table visual to cause the filter affect.

Does it make sense?

Nathan

[aa_KF]

Thank you again @natelpeterson! 

I've made the new measure in the first table (the one I want to ably the filter on)

But adding the measure as the Slicer value isn't possible, I think I'm missing something 🙂 Is it the relationship? 

Current Relationship

When I choose 'A' as the filter value, I only got rows with 'A' only, instead of any row that contain an 'A'.

Current Report After Choosing 'A'

Most appreicated!

[Anonymous]

There is no need of adding relationship. You are referring the filter column in measure. So can you remove the filter and try?

[Anonymous]

Try this and add this measure and T1[Values] column to visual without adding relationship to tables.

Filter Found Measure =
var text_to_find = SELECTEDVALUE(T2[Column2])
var text_to_search = MAX(T1[Values])
return FIND(text_to_find,text_to_search,1,BLANK())

[aa_KF]

Hi @Anonymous  The slicer still doesn't take the measure as vlaue 🙂

[Anonymous]

I was able to filter. Are you adding the measure to visual?

It's working

[aa_KF]

@Anonymous  It is working! Thanks!!

Is there a way that it could work without adding the measure in the table?

[Anonymous · Accepted solution]

It is not possible. But I could suggest you to modify the measure so that directly you can refer measure instead of the column. But there must be atleast a column from the table which you are searching.

Try this formula,

Filter Found Measure3 =

var text_to_find = SELECTEDVALUE(Table2[Column1])

var text_to_search = MAX(Table3[Column2])

VAR FIND_RES = FIND(text_to_find,text_to_search,1,BLANK())

return IF(FIND_RES >= 1, text_to_search)

Now you no need to worry about hiding.

Let me know if it is making sense.

Thanks

[aa_KF]

@Anonymous In case of having 2 Slicers for 2 diffirent columns like this: 

2 Slicers for two different columns

Is there a way where I can show values that is only have both filters apply as in AND between first values and seconds values? As a hierarchy filter maybe?Thanks!

[Anonymous]

You could add measure like this:

Filter Found Measure4 =

var text_to_find = SELECTEDVALUE(Table4[Column1])

var text_to_find2 = SELECTEDVALUE(Table5[Column1])

var text_to_search = MAX(Table3[Column1])                    

VAR FIND_RES = FIND(text_to_find,text_to_search,1,BLANK())

VAR FIND_RES2 = FIND(text_to_find2,text_to_search,1,BLANK())

return IF(FIND_RES >= 1 && FIND_RES2 >= 1 , text_to_search)

And result would be like this

did you mean this?

But please ensure that the filters are applied from different tables. Otherwise it won't work.

Hope this helps,

Let me know if it meets your requirement.

Thanks.

[aa_KF]

Thanks a lot @Anonymous !!

[aa_KF]

It makes lots of sense!

Thank you so much @Anonymous for your time and help!!

[aa_KF]

Hey @Anonymous  Thank you so much for your replay!

Still the Slicer isn't accepting the measure Filter as a value