Filter with Slicer

[Aykut]

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

[speedramps · Accepted solution]

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

[Aykut]

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

[mangaus1111 · Accepted solution]

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.

[speedramps · Accepted solution]

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