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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
andrecavaca
Frequent Visitor

Visual level filtering for slicers

I have a large set of options from which I would like to pick some specific ones and use them in a slicer to filter my data.

Why Is it not possible to add a Visual level filter for a slicer when this is an option for other chart types?

I'm currently using a 100% stacked column chart as a workaround.

 

Thanks,

Andre

1 ACCEPTED SOLUTION
austinsense
Impactful Individual
Impactful Individual

There is a hack for this ...

 

1. turn the visual into a bar chart

2. apply the visual level filter

3. turn the visual back into a slicer

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

View solution in original post

54 REPLIES 54
Capstone
Resolver I
Resolver I
JeffreyLeiseth
Regular Visitor

Goal: 1 Slicer returning ALL rows matching DATE1 OR DATE2 with SELECTEDVALUE

Have 1 slicer where a min and max date range is selected and use that to filter such that ALL rows with DATE1 OR DATE2 are returned.  Using two slicers returns only rows with DATE1 AND DATE2 which is why this is not the ideal scenario.

 

Example Details:

Table: CE      (All relevant fields are in this table)

DATE1: CreatedTime

DATE2: caseclosedtime

DATE1 fully qualified reference: 'CE'[CreatedTime]

DATE2: fully qualified reference: 'CE'[caseclosedtime]

 

Steps

  1. Create a new Column

in the function bar replace

Column =

With

CreatedTimeOrCaseClosedTime = SELECTEDVALUE('CE'[CreatedTime],'CE'[caseclosedtime])

Now we have a column that contains a date and will return created date if it is selected in a slicer and if not will return closed date.

  1. Create a slicer
    1. Select a blank  area of the page
    2. Select the slicer visual
    3. Drag the newly created column CreatedTimeOrCaseClosedTime into the empty Filter field
  2. Do something with it because you are done man. Lol
Anonymous
Not applicable

Any news on this topic ?

 

We need that feature a lot of times. 😞

 

It's really frustrating to see that something so basic is not implemented. I need the filter always to be updated to the current date i.e. all filter options.

Anonymous
Not applicable

Create two of the same slicers, making sure one of them (referred to as 'first slicer') only has 'interactions' with the other slicer ('second slicer') and nothing else on the page. Select the chosen items from the first slicer and it will filter the second slicer to only show those options available. Go to View->Selection Pane and hide the first slicer so it is no longer visible and you're sorted.

Anonymous
Not applicable

You can add a page level filter and remove what you don't want to appear in the slicer. This is really useful when your requirment is to remove (Blank) or empty option from the slicer.

Anonymous
Not applicable

If a direct page filter wont' help, then you are probably filtering the wrong table and a relationship is missing.

Anoterh way to help limit your slicer options is to ensure you have cross filtering enabled. 

often you have filters on the page, but the slicers seem unaffected by the filters. that's possibly because you're filtering for all won opportunities but are not applying that filter back to the accounts list. (just as a concrete excample I was struggling with)

if you configure your relationship between the two tables to not just filter in a single direction but both you will see that slicers automatically filter too. 

 

PS. this cross filter option often only works once. PowerBI does not like multiple cross filters in your model as it can only handle a singe filtering path. 

Anonymous
Not applicable

This was 100% what I was running into with trying to filter down my results in a table. Thanks a lot for the info! Works perfectly

AmitK
Frequent Visitor

I found a hack

My data has hospital Id in one table and hospital names in another table. To see only hospital for Ids present in table1, I added the hospital id slicer from first table and manually chose all the IDs (remember not to use select all also add a blank id in the first table  ),

add another slicer from the master table.The first slicer will filter the second slicer .hide the first slicer behind second slicer

 

shirtzel
Regular Visitor

I just went to the Ideas forum and upvoted as many posts as I could find that request this fix. This is so important! And it's so frustrating to see a Visual-level slicer on this visual, but not be able to use it!

 

In the meantime, I have a really hack-y workaround:

  • Right click on your Field, and group together all the values you don't want to show up in your filter. Name this group something that will send it to the bottom of your list, alphabetically (such as Z-Past Performances)
  • Leave all the values you'd like to see in your slicer Ungrouped.
  • In your Filter Slicer:
    • In "Field" enter your grouped Field

This will give you a Filter Slicer with all of the values you want to see, then a group of values at the very bottom that contains all the values you don't want to see. If you don't have too many values you do want to see, you can even format this filter slicer as a List and resize it so that all of the values except for the Z group at the bottom show up.

sumit4732
Advocate II
Advocate II

Hi,

There is a workaround to it: 

1. Create a copy of the slicer so you have two slicers on the page (let's call them "SL1" and "SL2")

2. On SL2, select the values you want to see appear in the actual slicer (this is NOT using the visual filter of the slicer, just select the values from the actual slicer)

3. SL1 will now be showing ONLY the values you have selected in SL2. 

4. With SL1 switch "ON" background and set transparency to 0%

5. reduce the size of SL2 to something smaller than SL1 and Drop it "behind" SL1 to hide it. 

This will get the work done.

Thanks @GregM for the edit 

Anonymous
Not applicable

Thanks @sumit4732  That worked... 

 

To be clearer for other users...

1. Create a copy of the slicer so you have two slicers on the page (lets call them "SL1" and "SL2")

2. On SL2, select the values you want to see appear in the actual slicer (this is NOT using the visual filter of the slicer, just select the values from the actual slicer)

3. SL1 will now be showing ONLY the values you have selected in SL2. 

4. With SL1 switch "ON" background and set transparency to 0%

5. reduce the size of SL2 to something smaller than SL1 and Drop it "behind" SL1 to hide it. 

 

Thanks again @sumit4732

 

Best Regards

Gerard

 

Thanks @sumit4732 It's a great workaround! Just wanted to add that you can hide SL2 in the Selection Pane, it will eliminate the need for step 4 and 5.

I just saw another option for this that @Mike_Carlo came up with. Since we can show and hide things on a page, he just copied a slicer, filtered it, then hid the slicer. The other slicer retains the same context without the filtered out value and no one is the wiser. A bit harder for someone else to discover 🙂 but its super quick without the need to layer things. As a seperate note I just thought of, it would be easy to just create a bookmark page that contained the filtered slicers, would be easier to discover.

 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Here is the tutorial on how to accomplish this: https://powerbi.tips/2018/05/apply-a-filter-to-a-slicer/
Mike Carlo ( https://powerbi.tips )
mike@PowerBI.Tips

This 'solution' is great if you have only a few visualizations that need interactions to be turned off. It is much more cumbersome of a 'solution' when you have a page of over 30 stacked visualizations. 

Anonymous
Not applicable

Hi Mike, 

 

Isn't your solution the same as using a Page/Report level filter? If not, what's the difference?

 

Thanks

 

Robin

@Anonymous The effect is the same, the difference is that the filters panel is available to the end user, and they can modify and remove those filters. This method effectively removes the capability of an end user to see or modify anything.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Anonymous
Not applicable

Both visual level filters and page level filters don't seem to apply to any slicer - even those that are downloaded from the marketplace.  

Anonymous
Not applicable

Any news on how to solve this one? Also need to use a filter version.

Anonymous
Not applicable

Me too Smiley Sad

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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