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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JLOV75
New Member

Display rows excluded by a slicer

Hi,

 

I'm hoping someone can help me.

 

I have a table that uses multiple slicers to exclude rows that don't conform to business rules. Each row has a unique serial number and depending on what slicers are applied, we may be excluding items that have not been purchased in the last 3 years, have a certain type of stock type, stock class or item owner.

 

I'm looking to have two table visualisations in the same tab; one displaying the items that conform to the selected slicer values and another that uses a measure as a visualisation filter that displays the items that were excluded.

 

I have seen examples to solve this issue using disconnected tables, however, I don't want the user to have to apply one set of slicers to get the included table and another set to modify the excluded table. 

 

I have mocked up a view of what I want the view to look like, obviously the "excluded" table isn't displaying correctly, but if it was, when you sliced by selecting "DAFL TYPE" - (blank), the sliced item would appear in the top table and the two remaining values with different "DAFL TYPE" values would appear in the excluded table. I would need this to work for any/all slicer that the user selects. 

 

JLOV75_0-1660181471356.png

 

 

Ive posted a link to a share folder with an example table; not sure if it'll work.

 

Thanks in advance

 

@https://drive.google.com/drive/folders/1np_YZkCMadVB2myTbQDzbuAU7LCdJlOQ?usp=sharing

9 REPLIES 9
TheoC
Super User
Super User

Hi @JLOV75 

 

Okay, managed to locate something I used sometime ago that aligns with what you are after.  Hoping it can also assist you in this:

 

https://datakuity.com/2019/06/13/power-bi-exclude-data-based-on-slicer-selection/

 

Given that you want a table that includes the selected items from the slicer and one that does not include the slicer, you may need to have two slicers on the page and just use Edit Interactions with your Table visuals to achieve the perfect outcome.  With the two slicers, one can be hidden underneath the other if you do not want them both visible (as the hidden one can be set to interact with one specific table e.g. included or excluded data).

 

Hope this helps mate.

 

Theo

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

I did find this one also and it works with a disconnected table; the issue is my actual table is about 500,000 rows and I have more table not present in the example the are many 100,000's rows each also so I'm hoping to achieve this without yet another 500,000 row table for this one function...

 

is it possible to use a variable in the measure to reference rather than a disconnected table?

Hi @JLOV75 

 

No worries at all. 

 

Before going any further though, are you able to provide a screenshot of your actual model and also and highlight the actual fields you will use in your visual Table and those fields that you will use as your Slicers.  Please ensure that the relationships that exist are all visible with the primary keys / foreign keys for each as well.

 

Alternatively, can you create a sample file that we can work off to help create a solution for you?

 

All of this plays an important part to ensure that we put together a solution that aligns with your needs in the most efficient way.

 

Thanks heaps and look forward to hearing from you soon.

 

Theo 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

No Worries at all; I've updated my original request to include a screenshot of the view I'm after and there is an example pbix file in the provided link that can be used. the rest of the tables I've been talking about are treated more as lookup tables and have little influence over the central table. So in theory, we should be able to work out this solution on this small example file and it will carry over to the full model.

 

Thanks for your help!

TheoC
Super User
Super User

Hi @JLOV75 

 

You can use Edit Interactions to achieve this without the use of code.

 

All you need to do is click on the Slicer, then go up to Format in the ribbon.  On the far left in the Format tab, you will see Edit Interactions.  Click it.

TheoC_0-1660180780817.png

Once you have done that, you can then go to the chart that you want to ignore the Sliver and click the "don't filter" button.

TheoC_1-1660180810866.png

This will hopefully achieve what you're after.

 

If you want to actually use code, you can use REMOVEFILTERS to achieve this.  Here is a link to another post that can provide a bit of additional insight: https://community.powerbi.com/t5/Desktop/Ignore-slicer-in-measure/td-p/1129414

 

All the best!

Theo

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Thanks, however, that will give me a complete list of all items which isn't what I want. I need a table that displays the items filtered (included) by the slicers and another table that displays the items the selected slicers excluded. 

Hi @JLOV75 my apologies.  Completely misunderstood!  

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

@JLOV75 the folder with the link is empty.  Are you able to double check?

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

sorry, try now

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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