Forum Discussion
Filter with Slicer
Hi there,
I have the following scenario:
I want to use a PickList, such "Slicer".
I want to be able to filter
- on individual Plants, such Apple, Asparagus, ...
- on Plant Type, such Fruits, Vegetables, ... and
- on All
Depending on the selection, corresponding aggregation should be calculated:
For example, the value 56 could be shown an a "Card" when user selects "All".
Without the selections "All", "Fruits", "Vegetables" and "-----"
I would just create a relation between [Picklist] and [Plants] tables.
But how would I create the aggregation with "All", "Fruits", "Vegetable" and "-----"
What would be the best practice?
Here are the codes I have:
[Picklist]
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jcy7CoAwDAXQf8ms4PsxquDq5lIcooQi1FKq/X/TNFlybkiUghIymIyBI1NQ8bz6cH+vsGbupOnD01CKGo7yWKI23jpnSNSxZrTcwp65ebQ6bQfmEq7wnOQlGOPx69CjDul5WcQPhJZ5/A==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"List Id" = _t, #"Plant List" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Plant List", type text}, {"List Id", Int64.Type}})
in
#"Changed Type"
[Plants]
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsKMhJBdKmSrE60UpGQJZTYh4QAhmGBmAxYyDTvygxLx2kzAgiZgJkOpcml+YmpRYBmcZgQVOQccUFiUWJ6aXFCkCOGVjYDGRmamJeMchII6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Plants = _t, Price = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Plants", type text}})
in
#"Changed Type"
Many Thanks,
Regards,
Aykut
Do it the easy way instead.
Simple create 2 columns in your picklist group(fruit, veg) and plant I'd
Then drag both the group and plant field to the slicer, to create a hierachy slicer.
- In slicer formatting, turn on multi selection and the all option
Congrats - you now select all, fruit, veg, plant or any combo
Hi Aykut ,
see my pbi file. I have uploaded a table with the hierarchie and then created a relationship with the fact table using TREATAS.
https://1drv.ms/u/s!Aj45jbu0mDVJi2SFHi7CadLh7-DF?e=QzQ2j4
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Do it the easy way instead.
Simple create 2 columns in your picklist group(fruit, veg) and plant I'd
Then drag both the group and plant field to the slicer, to create a hierachy slicer.
- In slicer formatting, turn on multi selection and the all option
Congrats - you now select all, fruit, veg, plant or any combo
Hi Aykut ,
see my pbi file. I have uploaded a table with the hierarchie and then created a relationship with the fact table using TREATAS.
https://1drv.ms/u/s!Aj45jbu0mDVJi2SFHi7CadLh7-DF?e=QzQ2j4
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I am amazed, how fast & good the community is with helping 👍
speedramps:
Thank you for the advice.
I wanted to avoid the hieracy slicer approach, but I guess this approach is the fastest way to solve this challenge.
mangaus1111:
Thank you also for your advice .
This is exactly what I was looking for. I have to admit, this approach with TREATAS is more time consuming. 🙂
I have a general question. This applies for both approaches.
In fact this applies to Slicer in general.
No matter if I select "All" or "None", I get allways the same total.I understand the reason behind, but how can I avoid this.
So how can I get for "nothing" selected => Total = 0.
Thanks, Aykut
