Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
79 | |
63 | |
61 | |
59 |
User | Count |
---|---|
166 | |
114 | |
100 | |
73 | |
67 |