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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Super User
Super User

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
Super User
Super User

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.