Nothing selected in Power BI Slicer? Then don't show data!

Answering one of your Power BI questions about how to prevent data in a visual if nothing is selected in a slicer! Challenge accepted!
Do's and Don't with cascading slicers in Power BI
• Do's and Don't with ca...
📢 Become a member: guyinacu.be/membership
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/courses
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #Slicers #GuyInACube

[@mabuelhagag]

I'm pretty new to Power BI and has been stuck with this and had to implement it. Thank you you're a life saver!!

[@darshanhc1360]

Awesome. My fav PowerBI youtube channel. Whenever am stuck with bug or development, i come here. Great keep doing this.

[@ProtValnus]

Thank you for this. Largely resolve the problem. To give some context for everyone. I have a year slicer and a company slicer ( not the same table ) If there is no year selected the entire report is blank . I ended up adding the IsYear filtered to my second slicer for company selection. This made the logic flow work of selecting you reporting year then the company ( it can work either way ) The exception is a matrix with a drill down column. I created an IsCompany filter but that visual shows data and only stops shows when year is blank Anyway, was super cool of you to help me out like this and expand the general info out there.

[@jacquelinelacan]

Thank you yet again. Have done something similiar in past, but this takes why I achieved even futher. And INT(ISFILTERED()) is pure gold. Thanks Andreas!

[@williamleveson-gower5088]

Love your quick, down to the point, effective videos !, this tip is awesome !

[@rauljimenez5485]

A thing that I normally do for that case is add an explanation in red & bold text on the graph: " To display data choose something in the slicer". That way people don't get confused thinking it's empty space. (I display a measure on a card "under the graph", the measure returns an empty character when there is something selected. If you place it over the graph it will prevent things from being highlighted on the graph). I hope this makes sense

[@successfullife7689]

How to make a slicer that effects one visual. The idea is to create two visuals having the same measure, the left slicer affects the left visual and the right slicer affetcs the right visual ; this way I can compare the sale of one product in two different years and compare discounts and other stuff. It's more of a simulation than simply showing data. Amaizing video Thank you very much.

[@jovanapavlovic386]

Dude I was proud to think that I was the only one who came to this brilliant idea 😂 But I didn't stop there, also added two cool triggers inside the same visual: 'Please make a slicer selection' and 'No data available for this category'.

[@johnabram4159]

share it please

[@antonycatella5901]

I did something similar as well. Included textbox with warning messages and warning message in the title of dependent slicers

[@fillipemartins8357]

Incriveble!! You're insane in Power Bi! Thanks!!!

[@olemew]

Notes to self: - It has to be INT(...) and not the boolean directly because of a bug. Bool measures just wont filter the visual (dropdown is frozen). - This thing implies that the chart is not using the field on the slicer. If the chart uses the slicer, the two filters get mixed and wont do what you wanted. - ISFILTERED returns false if you have not selected anything, but also when you "select all". It's not about the cosmetics of the slicer. It's about the data model.

[@_irietek_]

This needs major upvotes. Your second point solved my issue!

[@andrescarvajalmujica203]

Excelent Tip! Thank you very much, i ll use it in my report

[@alissonalpino]

Thank you! Simple and solve my problem.

[@yekhtiari]

Short yet extremely helpful.

[@UrszulaPukalska]

I have looked for something similar for case when you have multiple slicers (I have 11!). So, I created measure with to variables: TotalNumberOfRows = Calculate( Countrows(Fact_Table), ALL()) FiltredNumberOfRows = Calculate( Countrows(Fact_Table)) RETURN(TotalNumberOfRows-FiltredNumberOfRows=0,0,1) Not sure about performance, but now it works ;)

[@khaledtellopalacios3072]

Thanks

[@kassiowifried]

Thank you

[@williamharris1752]

Thank you!!

[@sluciano]

Thanks for the video!

[@Real28]

That's fantastic for certain use cases.

[@shivanihandore4738]

Thank you so much 😇

[@tibortot8148]

Brilliant! Thank you very much.

[@JohnOttoKnoke]

Hi Patrick, it worked great for me, thanks. You help me to solve an issue.

[@tkadosh]

Simple but amazing tip !!

[@justsee.5773]

Helped alot thanks

[@rohitwattamwar1]

Hi Patrick and Adam, Great content as always. I was wondering if you can create a video on How to distribute Power BI reports with large audience, best practises of it, how to manage the dataset accesses, report accesses & Power BI Apps accesses effectively, that would be great. And also, even sometimes if the dataset or dataflow owner is not available in organization anymore, then how can we manage take over part or is it possible to organize this with Service Principals, Any automation using Power Automate, etc. Basically a report distribution life cycle after report creation, with all the smart work involved in it. Thanks and regards, Rohit

[@graceliu7600]

Thanks!

[@user-sj9xx3xn2n]

Hi Patrick, this approach works only for small visuals. I have an Ad hoc table with two slicers, one for dimensions and another for measures. With such conditions, the table doesn't show anything, but it still makes calculations. Due to this reason, I often get the message "Lack of memory," etc. I guess I need to add dummy values for both slicers and select them by default. After this, I will change the condition to show data if the selection does not equal the dummy columns.

[@aejaekyu-13]

you just earned a new subscriber today. thanks so much!

[@wmfexcel]

That's banana! Thanks for the tips! To "avoid" filtering upstream, I prefer simply use "Edit Interaction" to disable the interaction to the destinated filter(s). :)

[@91221srikar]

this helped, thank you so much..

[@majdyazigi8185]

Cool light one!

[@leonidiakovlev]

Quite nice! Doesn't work though if you want to see the categories in the visual (in this case Category is always regarded as filtered).

[@elhassanaitelhadj8796]

that's soo cool and useful, thanks

[@rajkumarrajan8059]

Lovely trick

[@David.Donoso]

Hi Patrick, can you make a video to show how to control days between stages in a sales process? I have one fact table with different phases before achieving a sale, but want to measure the average days between the keep track of SLA. Thanks and great job!!!

[@aquabestindian]

Awesome! It works BUT I can't add that measure as a filter for the entire page or all pages. It can only be added in a particular visual (or add to each visual individually)

[@mohicandread1557]

Yoooo great video thanks for sharing. In case anyone else like me stumbled on here for a solution to applying this to card visuals, please see my comment below: If you change your card visual to "Multi-Row Card" in the "fields" section drop down, select "don't summarize" then you'll be able to change the "Show items when the value" to "is". For some reason you can't alter this on a normal card old or new!

[@aishwaryabanerjee6466]

Could you please explain as I am unable to edit this measure in the filter visual to "is"

[@brypie04]

Great tip! You could also add a card visual with some text like "Please select from the filter!" and have it being displayed when the flag is 0 - that way the user knows they have to do something, rather than just having a large empty space...

[@enfafil1]

Hi Bryon! How do you make it work for the card as you described here?

[@brypie04]

@@enfafil1 I take it back - it didn't work! I was thinking we could just do the same and use the same 0/1 filter for displaying the card.

[@clairedouglas5279]

Thank you! I was looking for a solution everywhere, and this made it so easy. I ran into one issue though... it didn't seem to work for me when I applied it as a filter to a multi-row card visual.... any thoughts on why this might be the case??

[@jyothiswarooparajugoparaju2629]

Good one

[@atulk9122]

Hi Patrick this video is great. Could you please make a video that how to capture paginated report DAX queries when we run the report. If my report run slow I need to know how to check what is making it slow?

[@datasciencecamp]

You are awesome....

[@emilyjvirtual]

this is great for the multiple graphs visuals I have, but it does not seem to work when using card visuals. Does anyone know a resolution for this?

[@mohicandread1557]

Not sure if you've found this solution yet, but what I've just done works. If you change your card visual to "Multi-Row Card" in the "fields" section drop down, select "don't summarize" then you'll be able to change the "Show items when the value" to "is". For some reason you can't alter this on a normal card old or new!

[@PavanSrk]

Awesome feature and this helps a step forward in visualization Thank you But how to apply for Visual ID Card tile, tried many ways but unable to solve it Can you please guide me on this Thank you in advance

[@aytugokumus7537]

Hi Patrick Thanks for nice video. Really helpful. I have a question & problem. Would be great if you can explain how to do that.. Question ; I have an excel table for projects, about 100.000 rows, with 10 columns that specifies the project specs. Question is ; In Power BI, i will choose one project from table then i would like to find most similar projects from main excel table according to similarity based on column data (each column will have weighted similarity factor & column context can be number & text). And then i will filter & list them based on more similar project to less similar project by similarity score. ?

[@kamcike]

Great video as usual guys! One question about the permissions on the service. I have troubles to find out how to set up an edit right for a report which is not in an app, is in a workspace to which I don't want to give a user contributor rights - no need that the user should see a content of the whole workspace. The user has rights over the dataset which is actually saved in a different workspace and has also an app. the report is basically a testing report in testing environment created from a published dataset in a different workspace. Seems like it's not possible even in 2022? Any ideas?

[@walteradamsbe]

The cross-highlighting thingy Patrick is talking about at 2:55 is why I hate slicers so much. My reports could always benefit from interconnected slicers but the cross-highlighting mess makes it impossible to use. I would go as far as to label this a design flaw in slicers which I hope someday they will fix.

[@kos2932]

amazing. only one in internet explains this

[@ColinHershberger-bu7ej]

Q: Do you know how to apply this to matrix tables and what about measures that combine information into one?

[@madhusmitadas9919]

Thanks for sharing the tips. Can you also show how to tackle this issue when we have multiple slicers for one Visual?

[@RodrigoBorges-zh4ru]

Thank you! This is the best PBI channel, congrats! Just one question, I am trying to use it with Cards, but It doesn't allow me. Is there any other way to work with cards? (in my example, I have specific customer information on a card (Active and Inactive) and I don't want it to pick the first record considering all customers. Thank you!

[@fazilkians]

Hello, what is the way to export a well formatted table from power bi to excel file? It gives only csv option which removes all the formatting

[@jyothiswarooparajugoparaju2629]

Can you help me how to capture the from and to value of a numeric range slicer in power bi

[@priyankajain6625]

Thanks! Getting a blank option added to the list of slicer after using this tip. Any suggestions how to avoid it? I have made sure that in the join there is no unrelated data

[@prakamnigam]

One issue that I have been facing is that if I have nested slicers and I have something selected in upstream and downstream slicer then changing selection in upstream slicer changes selection option in downstream slicer but retain one option from previous selection also.

[@ferdinandsantos6680]

Hi Patrick! Is it possible to single select a quarter from a slicer but then always show five quarters of detail rows (starting with the selected quarter) in the table visual? For example, if I select Q3-2022, I want the table visual to show all rows for Q3-2022 to Q3-2023 (07/01/22 to 09/30/23). If this is doable, I would appreciate very much any guidance on how to go about it. Thanks in advance!

[@manjunatharalikatti2202]

Hi A/P great content, When we select Drop Down in slicer than "All" option is not getting hide, as we can see in larger list, Can you please explain if that is possible ..

[@eamonheenan5054]

I finally got it to work but I needed to use a disconnected slicer for the filter.

[@manikantabalusa6896]

Can we drive the data to slicer by selecting a record over table visual?

[@shivashankarable]

Please can you enable select all option in the slicer and check if ISFILTERED() is working when Select All option is selected

[@theoneandonlyshelley]

How did you do the blue indicator above the visual!? I'd love to learn how to do it

[@blofeld2430]

Hello Patrick, first thing first, just wanna tell you that you rock the entire Power BI community :) I'm currently facing a challenge and it's fixing the selected value in a slicer. Basically, I have a slicer that shows purchase year (unique value selection), and I want the selected value to be the latest whenever the report is refreshed (for now the selected value is the one the last used picked). Is that something you've dealt with in the past ?

[@pamelabrownfield5145]

This was a great video helped a lot /the only problem is / I’m not using a slicer/ I’m using a text filter and it won’t work with a text filter any advice?

[@thetoediastv]

Hi, thanks for your video. I have a question. I have 3 stages, Concept, Plan and Execution, for my project, I have put them in a filter in excel, now currently all activities are in the Concept stage, I want to show all the stages on the dashboard even though at present all activities are in the Concept stage, I want the dashboard to show all the other stages. I tried the option "Show all items with no data", but that doesn't work, any help please, thanks

[@rodrigoamandio5613]

Is there a way of doing it in a dashboard using a Direct Query connection?

[@granand]

Hey Patrick please point me to video where I can set default settings in slicer

[@nivedithas687]

I have a table visual that needs this feature. But the problem is I also have category field in values of the table. So this does not work. Any workaround if category column is included in visual field list ?

[@nitashatiratram3184]

What about date slicers, is there some magic trick for reports showing prior month visuals or results. is it possible to notify the user " hey this is last months view, change the date slicer to see the latest results" Thank you for you amazing, engaging teaching style.

[@gentlesoulolayemi3566]

Thanks alot! but how can I return a string of "NO FILTER IS SELECTED" on text and also when select to show the string selected

[@ArjunKrishnaUserProfile]

this approach still sends over the dax query to shared dataset for calculations. Is there a way to tell it not to send the dax query to the server if isfiltered returns false?

[@davidghabbour2000]

Hey, can I do sumifs in PowerBI without grouping tables? as I need my raw data as is?

[@KUDDELofDTH]

That DAX keeps visuals such as bar charts from loading, if no filter used, ok. But it seems that this does not work for simple tables. They will always load. How can I use this way to keep tables from loading as well? Or is there a different solution? Thanks alot!

[@jyotisoni1530]

is there any workaround in case of paymeasure too? with this solution I am able to filter the table but the headers are still there in table.

[@themyuhas]

So what if I want to add the 'Select All' option to my slicer and have the same result? Meaning I don't want anything in the table(s) until a selection is made in the slicer, whether it be the select all option or a single selection.

[@alanpjames9919]

Hai@patrick, I have challenge in dax could you please create video regarding this scenario is If nothing selected from slicer visual the card or the chart should give a static value 195 ,if user select any particular year or any area they want to see the actual target value (actual data) for that particular area . ie if nothing selected static value if they select slicer they want to see actual value in kpi card .could you please help me with this

[@ellen4618]

Hi great video, but this doesn't seem to work on a card visual. Could you please resolve that problem/mystery?

[@user-ot8ej9eb2o]

Hi Patrick, thanks for this - but I still have a problem with it. It works as you have shown it for me as an admin, but I have a report using RLS, and lets say for example "Person A" opens a report which shows them data for "Department C", my RLS defines that the report opens up automatically showing them data for "Department C", but the Dept C isn't necessarily selected in the slicer so the visual is still blank. How would I get around this problem ? Thank you so much in advance :)

[@dominikpetri2120]

Isn't it possible to omit the INT() and use IsCatFiltered equals true (instead of 1) Would save the INT() calculation, wouldn't it? 🤔

[@rejji]

This is good and followed what you have done but my visual did not work. It still behaves the same as default

[@anon746912]

Doesn't scale well for large direct query tables. Would be cool if there was a native way to do this.

[@danyell12345]

Does this only work using slicers? I have a filled map that I want to use as my "state selection" rather than a slicer, but I can't seem to make this work when selecting a state to only show the values that are within that state.

[@darrenjenkins924]

I have been trying to do this with multiple dynamic parameters. I had to correct a field call no selection and set it " ". However my row counter doesn't work when counting on multiple dynamic parameters created from one flat file.

[@parinitamaithil7779]

Its great help, But this filter is not working on Matrix visual, Card Visual and Bar Chat. Can you help ?

[@akpokemon]

Did you find a soltuion? It's absolutely infuriating that this can only be applied to _certain_ arbitrary visuals, instead of any visual...

[@mohicandread1557]

@@akpokemon if your after a solution for the card this can help! Not sure if you've found this solution yet, but what I've just done works. If you change your card visual to "Multi-Row Card" in the "fields" section drop down, select "don't summarize" then you'll be able to change the "Show items when the value" to "is". For some reason you can't alter this on a normal card old or new!

[@supersayan9888]

What if you want to sync the category between the chart and the slicer. That measure wont work, because it filters trough the same column, therefore its always isfiltered is always true.

[@azzysteel7735]

Hi guys, great content as always. I don't suppose you have found a way to hide or un-select the previously selected option in a slicer, when a higher heirarchal option is selected in the other slicer?

[@kristabethable]

I need this solution too!

[@xplicit4581]

Been looking for this for so long, thank you! But I have an additional question. I want to use this filter in a card where a date or value (min of) is shown (for a contract for example). I can add the measure to the visual but I can’t change that measure in the filter. Is there any way to make this work?

[@Chris-tj4es]

Hey @xplicit4581, having the same issue. Have you found a way to solve it ?

[@CasagrandeGui]

I tried do this with a Slicer versus Card, and unfortunately doesn't works! Anybody have some idea?

[@JoshLundmark]

Not sure if this is the best place to ask this question but I figured why not try. I have a request to build a few reports using a SOAP API. I haven't found any good resources or tutorials on this. Any help here would be greatly appreciated!

[@tanweerabbasabbas649]

I have a bar chart with countries and units sold . In smart narrative when nothing is selected it still shows any random country but with correct total units for all countries. Plz advise bro😘

[@narendrababu2287]

For card visuals it will not work. Can you please show another solution

[@seabreeze58]

How can you do this for cards because I tried but doesn't give me the option to change to ''is'' or to fill in an number in the filter beam?

[@aishwaryabanerjee6466]

Not working on a multi card visual, as the measure breakdown is frozen, What to do in that scenario

[@tareksalha]

I actually like to implement this technique directly into the measure I want to display. That way, I do not get these kind of weird interaction at all.

[@matthewbelitz2950]

doesn't work for me, i add to my filters but i can't change the drop down in the filter itself to enter "1".

[@planodoprojeto]

Can we do this using table, no slicer? Table to Table...

[@ewelinawiesiolek2514]

What if you do “select all” on that slicer with categories? From what I remember it would also be treated as not filtered therefore it would not show any values

[@antonycatella5901]

You can use 2 conditions: 1 checks for 1 value selected, the other checks if nothing is selected then do the logic

[@poojasake7483]

how to apply for date range slicer

[@munnivlogs5025]

But ..after using this... When I select all in the slicer .. then visual is showing blank

[@lucasrezende738]

Really cool stuff, but why doesn't work for Card or Multi-row Card visuals? is there a way?

[@suyashchavan1752]

Did you find answer to this please share i need it

[@ruthporter1102]

Can you do this with a card visual instead of a graph?

[@suyashchavan1752]

Did you find a way to do this i need help