Filter Formulations

[Querymaster]

I have this table:

SampleID	Ingredient name	Ingredient weight%
A	abc	10
A	def	15
A	ghi	25
A	jkl	40
A	mno	10
B	ghi	40
B	jkl	20
B	pqr	40
C	abc	15
C	ghi	35
C	stu	60

I would like to use a slicer to search for ingredient names. E.g a search for 'abc' should result in:

SampleID	Ingredient name	Ingredient weight%
A	abc	10
A	def	15
A	ghi	25
A	jkl	40
A	mno	10
C	abc	15
C	ghi	35
C	stu	60

but it ends up in:

SampleID	Ingredient name	Ingredient weight%
A	abc	10
C	abc	15

2 questions:

1. How can I filter but still see all Ingredients for the samples that contain the search item?

2. How can I search for samples that contain both 'abc' and 'ghi'?

[v-diye-msft]

Hi @Querymaster ,

If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!

Best regards,

Dina Ye

[v-diye-msft · Accepted solution]

Hi @Querymaster 

I created the sample as yours , and add another table with only one column [Ingredient name]using for slicer.

Then add the measure:

Measure for weight% = var a = CALCULATE(MAX(Table1[SampleID]),FILTER(ALL(Table1[Ingredient name]),[Ingredient name]=SELECTEDVALUE(Table2[Ingredient name])))
Return
IF(SELECTEDVALUE(Table2[Ingredient name])=BLANK(),MAX(Table1[Ingredient weight%]),CALCULATE(MAX(Table1[Ingredient weight%]),FILTER(Table1,[SampleID]=a)))

When you use the slicer to select both 'abc' and 'ghi', actually it means resulting in weight% which satisfied both 'abc' and 'ghi', it will return nothing. Thus we’d better use the measure to create the “Or” relationship

Measure for "abc"&"ghi"= var a = CALCULATE(MAX(Table1[SampleID]),FILTER(ALL(Table1[Ingredient name]),[Ingredient name]="abc"||[Ingredient name]="ghi"))
Return
CALCULATE(MAX(Table1[Ingredient weight%]),FILTER(Table1,[SampleID]=a))

Best regards,

Dina Ye