Filter measure "starts with"

[yippiekyoo]

Hi All,

working currently on a project where I need to filter certain values from a database in order to categorize them. 

I need to have a measure that says filter all part numbers starting with the letter A. 

Thats my current function:

Filter function =
SUMX(
FILTER(
'AO Domestic',
'AO Domestic'[Part Number] = "STARTS WITH A"
)
,
'AO Domestic'[Total Actual Cost incl. Retro. Pay])

Would be great if someone could tell me how to correct the measure. Thanks a lot in advance.

Kind regards

[Heinrich]

Hello @TomMartens 
This solution is great.

Can this measure be used to filter and leave only the alphabetical rows.

Means filter out everything that start with A to Z
Regards

Heinrich

[TomMartens]

Hey @Heinrich ,

you can adapt the measure like so:

Measure 3 = 
CALCULATE(
	SUM('FactWithDates'[Amount])
	,FILTER(
		'FactWithDates'
		,LEFT('FactWithDates'[Item],1) in { "A", "B", "C" }
	)
) 

The curly braces define a table. Of course you can define a table in your semantic model that contains the rows A-Z, then the filter statement will look like this:

...
,LEFT('FactWithDates'[Item],1) in VALUES( 'tablename'[columnname] )
...

Hopefully this will help to tackle your challenge.

Regards,

Tom

[Heinrich]

Hi @TomMartens 

Thank you very much.

Have a great weekend.

Regards

Heinrich

[TomMartens · Accepted solution]

Hey,

you can write your measure like so

Measure 3 = 
CALCULATE(
	SUM('FactWithDates'[Amount])
	,FILTER(
		'FactWithDates'
		,LEFT('FactWithDates'[Item],1) ="A"
	)
) 

Hopefully this gets you started

Regards

Tom

[yippiekyoo]

Hi thank you very much  - that works.