Forum Discussion
Filter Formulations
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'?
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
2 Replies
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
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