Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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'?
Solved! Go to 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
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
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
User | Count |
---|---|
103 | |
90 | |
78 | |
72 | |
68 |
User | Count |
---|---|
108 | |
96 | |
95 | |
74 | |
71 |