FILTER by containing at least 1 instance of characters

[Anonymous]

Hello world, 
I have this table

Supl Name	Dep
GER	ST1
GER	SR1
GER	SR2
GER	SR3
GER	SR4
GER	SR5
GER	SR6
EGR	ST1
EGR	ST2
EGR	ST3
EGR	ST4
EGR	ST5
EGR	ST6
EGR	ST7
EGR	ST8
REG	SR1
REG	SR2
REG	SR3

and I want to filter every supplier that has 1 instance of character sequence, in this instance, ST.
So e.g GER that is connected with ST1 should remain, along with with EGR, that is connected with ST1-ST8, but REG should not be shown. 
So the final table should be something like this 

Supl Name	Dep
GER	ST1
GER	SR1
GER	SR2
GER	SR3
GER	SR4
GER	SR5
GER	SR6
EGR	ST1
EGR	ST2
EGR	ST3
EGR	ST4
EGR	ST5
EGR	ST6
EGR	ST7
EGR	ST8

Thanks in advance world.!

[Anonymous]

Create a new column in the table (best in Power Query, not in DAX) with the piece of text you want and then just filter by it.

Best
D

[Anonymous]

@Anonymous sorry but I didn't really get what you mean. Also, due to the use of Analysis Services, I can only create/filter measures, I got no access to the model itself unfortunately.

[Anonymous]

You can create a measure but it'll be SLOOOW. Please check the DAX function SEARCH. But this is a pretty inefficient technique.

Best
D

[Anonymous]

Thought so, but could you please give an example for a case like that, as I see that search is giving the starting position of a string , rather than filtering columns. 
I was considering a approach with something like allselected and a filter inside the aggregation of the calculate fuction but haven't made any progress with this concept.

[Anonymous]

This is the syntax:

SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])

and here's the page:

https://docs.microsoft.com/en-us/dax/search-function-dax

Best
D

[Anonymous]

thanks @Anonymous , I have seen the documentation of the command and I have great doubt about the use of it as it will indeed assist me to give me the position of the elements that they have the desired characters but it will not keep the mixed cases (See GER in my example tables), as it will show zero in the repective lines and it will filter them out.

[Anonymous]

Are you doing this in Power Query or in DAX? When you say "filter rows out", do you want to do the filtering before the data is loaded into the model? 'Cause I don't quite get it...

Best
D

[Anonymous]

@Anonymous the calculations are being done in DAX cause I got no access to power query , it is analysis services connection and company structure doesn't allow that unfortunately..
Sooo..!
Yeah, I would like ot do it after the model is being entered in the system, maybe through a measure that would be used as a filter (just sayin, pretty new in PBI, pretty old in Tableau)
and unfortunately , search doesn't work in my case, the Dep table doesn't pop on the quick selection menu, so I guess it is because it comes from a dim table. 

[Anonymous · Accepted solution]

If you want to do it through measures... here's the solution. You'll need to wrap all your measures the way it's done below:

// Assumption is that Suppliers
// is a dimension and that SupplierId
// is the primary key (or unique key).

[Measure Re-engineered] =
var __suppliersWithSTinDep =
	CALCULATETABLE(
		VALUES( Suppliers[SupplierId] ),
		SEARCH(
			"ST",
			Suppliers[Dep],
			1,
			-1
		) > 0,
		ALL( Suppliers )
	)
var __output =
	CALCULATE(
		[Old Measure],
		KEEPFILTERS(
			TREATAS(
				__suppliersWithSTinDep
			)
		)
	)
RETURN
	__output

You need to do this for each and every "Old Measure." The new measure only ever takes into account suppliers that do have at least 1 "ST" in the entries for Dep. However, you should not store suppliers the way you do. This is not really a dimension table, it's a bridge table. If there's a many-to-many mapping between suppliers and deps (as you seem to have), then you need 2 dimensions: Suppliers (where SupplierId is different on each and every row) and Deps with only the names of the deps (and their ID's). Then you should have a bridge table that would join suppliers to deps. This is the correct setup. But the measure wrap above will work for the bridge table correctly (even though I wouldn't do it myself).

Best

D

[Anonymous]

Seems like something that could work, I will play a bit around with this idea and I'll come to back!
Thank @Anonymous !

[Anonymous]

The fact that it returns the first starting position is the key to filtering. If it does not find the string, then it returns... what? Use this to your advantage.

Best
D