Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
Aykut
Frequent Visitor

Filter with Slicer

Hi there,

 

I have the following scenario:

Aykut_6-1668977333478.png

 

 

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".

 

Aykut_9-1668977548218.png

 


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

 

2 ACCEPTED SOLUTIONS
speedramps
Community Champion
Community Champion

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

View solution in original post

mangaus1111
Solution Sage
Solution Sage

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.

View solution in original post

3 REPLIES 3
Aykut
Frequent Visitor

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.

Aykut_0-1669022757073.png

I understand the reason behind, but how can I avoid this.
So how can I get for "nothing" selected => Total = 0.  

Thanks, Aykut

mangaus1111
Solution Sage
Solution Sage

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
Community Champion
Community Champion

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

Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

Check out the May 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.