Why is there a blank in my Power BI slicer???

Ever wonder why you have a blank in your Power BI slicer? Patrick shows you what is going on and how to fix it!
Download sample: guyinacu.be/blankslicersample
📢 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 #Slicer #GuyInACube

[@owenlamont4397]

I had no idea you could copy out the DAX queries for the visuals. This video was worth it just for that.

[@GuyInACube]

Yup. Love the Performance Analyzer! 👊

[@RH-nk7eo]

Yeah I clicked thinking oh I actually know this one but still learnt something new.

[@RoeGabriel_i_EMPOWER]

I LOVE you guys!!! Thanks so much - you make it easy!! I'm brand new to POwer BI, have a little power query / pivot experience - you are my lifeline and you ALWAYS have the answers!

[@randalltucker9343]

That’s a pretty good lesson on the value of paying attention, and listening to what the tool is telling you. Learning how to copy DAX code for visuals is an additional benefit. Great video!

[@brad7648]

Super helpful video. I just started using Power Bi and this saved me a lot of time! Thank you ❤

[@tacijjola]

Thank you Patrick!

[@borkasiamese4617]

Thanks for the lesson Patrick! Oh btw, having Blank values does not depend on the relation cardinality. I do not know why they explicitly indicated 1:many in the doc 03:07, but we can have Blanks even when the relation is 1:1

[@intangible_kant]

Just starting out in PowerBI, this answered my question perfectly. Thanks for the video!

[@ADITYA1234588]

Great tip. It is really important to address issue from root level. Kudos!!

[@pabeader1941]

Just wanted to let you know that you helped me today. I was fighting a dang BLANK and remembered that you had done a video on it. Used a few of the non DAXStudio hints and found it. So Thanks!!

[@AshutoshDeshpandeOfficial]

Always something new 😊 Before watching this, I would generally simply filter it out. But now I know to look more carefully into the data.

[@GuyInACube]

YES! Try to fix the bad data if possible. 👊

[@Zrall]

THANK YOU

[@waynethurman2144]

Thanks!

[@sajidbashir12]

Thanks Patrick. really good explanation

[@adanielmacedo]

Thanks for sharing...

[@kabis4572]

Thank you so much. I was just filtering out these blank values - just thought it was a quirk of Power BI. I shall go back and check them ! Nicely! 😀👍

[@lumiareize2957]

Man, thank you soooo much! I did the step-by-step you showed and it worked wonderfully

[@ronioslerne6813]

Thank you! great video!

[@damianchrystie4384]

Excellent Patrick.👏

[@TheNovember6]

Super handy, thanks Patrick

[@rschloch]

Yes indeed, I’ve encountered it. I’ve always just filtered it out on the visual. At least now I know why this occurs, and when I should be concern about data loss. Thanks! :)

[@dilmatz]

First time viewer, now a subscriber. In the first THREE MINUTES, I learned about DAX Studio and dax.guide. THANK YOU!!

[@GuyInACube]

Amazing tools! Glad to have you on board 👊

[@helloranjan89]

This is awesome. We only got this issue last night & now I know what is wrong. Thanks Man

[@sue_bayes]

Brilliant as always, loved seeing how you used DAX studio to investigate the data.

[@metacafevideo]

Lovely, I like the way you explain, that makes it very easy to understand. Keep it up.

[@COOKINGBIRD]

Thank you..

[@mramos01]

Its all about relationships! Great video. Usually, i click on the relation on data model, and adjust a filter from one table to another.

[@mohammedsaeed905]

Very helpfull, thanks

[@bhavinihasmuclalvassaramo]

Big thanks Patrick, this really saved my model!!!

[@christianlira1259]

Very useful Patrick and thank you for putting this video together. Much appreciated.

[@GuyInACube]

Most welcome! Thanks for watching 👊

[@mukuldevbisht2672]

Awesome! much needed video.

[@red77ashley]

You are brilliant! I love your videos and they have helped me so much. Thank you!

[@lunedfrancis2768]

Thanks Patrick, was actually a customer sort I set up for a graph that kept showing a blank. This video helped me realise that power bi decided to automatically build a relationship in that wasn't needed and causing the issue 😅 guess I should have picked a different column name

[@robcaguirreg]

Great video. Great lesson I learned

[@mashhc16]

Thanks, helped me solve a problem!

[@aleena1549]

I got this problem just today, i filtered out that black to make my report. Got to show it to client tomorrow. Thankfully now i know the problem and can handle it before showing it to client. Thanks

[@deanj5961]

Thanks Patrick- this is just what I needed today!

[@GuyInACube]

Awesome to hear Dean! 👊

[@scottjeffries6682]

Thanks, Patrick! Solved my problem before the video even ended!

[@ketanpatil3489]

thanks for this tip Patrick!!

[@GuyInACube]

You are very welcome! Thanks for watching. 👊

[@jamezday]

Great video

[@shekharkumardas]

I facing this issue today morning, great timing of video

[@GuyInACube]

Always love to hear that 👊

[@kishoreyc]

Hey Patrick, First of all, a BIG thank you for making such informative and relevant video tutorials. My PBI models have improved a lot... because of you and Adam. I have a Q. Can I build a Power BI model with lot of rows in a table ? I know i can build one easily in power pivot, however since the Power BI canvas is very small to put in a lengthy table (50 rows and 10 columns), can i graduate my model from power pivot to PBI ? Could you please guide me to any example model if this has been achieved already..!!

[@Meattrapper]

Ha! I was just looking at this issue this morning - thanks!

[@GuyInACube]

BAM! Glad it was timely 👊

[@rcbcb8468]

Great videooo... Thanks

[@meetdenis82]

As always, a very insightful video. You both are super!

[@GuyInACube]

Much appreciated! Thanks for watching 👊

[@kennethstephani692]

Great video, Patrick!

[@GuyInACube]

Appreciate that Kenneth! 👊

[@johanynavarro8065]

Love your videos Patrick!! some help for a newbie in PBI, how to filter a table by blank dates?

[@SaniGarba]

Instructive. Thanks

[@GuyInACube]

You are very welcome! Thanks for watching 👊

[@Amr-Ibrahim-AI]

Thanks for the practical tip I love the new intro, too :)

[@jpb2085]

Sublimely well demonstrated

[@GuyInACube]

haha thanks! 👊

[@margaritagrigaleviciute2369]

Hi! thanks for the video! What if blanks are expected? I have multiple fact tables, and some do not contain the data.

[@ihsan.l9375]

You are the teacher that i strugling to find!

[@GuyInACube]

wow! Thank you so much 👊

[@chandramohanbhosale8462]

This is very helpful. I was annoyed by blanks.

[@sagaon4]

I loved the intro, I work there! 😁

[@charlesd820]

Thanks Patrick. I have good data on both sides of my relationship, but still get a blank value in the slicer using Direct Query. Other than filtering out the blank values in the slicer is there any other way of investigating why the blank appears? Thanks

[@mayzhang6267]

Thank you so much Patrick! It's very helpful! I got an another problem. There's no blank value in the slicer but after I applied the dynamic RLS, the blank value showed up in the slicer. I couldn't figure out the reason. Could you please help me with this? Thank you so much!

[@fabiolazza8182]

Hi Patrick, first of all thank you so much for your wonderful video. I have the same problem but with reverse parts or I have more types of "customer_id" in the DIM_Customer and not in the Fact_Sales. How can I solve the problem?

[@michalroesler]

Yes.

[@spcherub]

Patrick - thanks for the simple, yet excellent, explanation of the problem and solution. I am facing a similar issue, but am looking for a different solution for my particular use case. Translating to your example data set, I'd like to find a way for the incomplete Client table to be the main "driver" of the table - basically only show sales for clients that exist in the client table. Is there a way to force an Inner Join to make this happen?

[@TainuiaKid1973]

In Power Query, merge the tables and then filter out those without a match.

[@johnnychallita6772]

Hi Patrick! Thank you for a this great video and channel!! I tried the solution you have in your video but I keep having blanks. Unfortunately, i once refreshed my visuals, after fixing the blanks as per your video, while keeping the blank selected in the filter pane. Now i can't remove them in any way although i am 100% sure my data is clean. Can you please help? Thank you!!

[@gondebas]

As a Power BI developer you will rarely have the opportunity to fix the data. What I am doing in that case is that I change all the missing values in incomplete dictionaries to "UFO" value. In this way you are able to complete the job. Usually those unidentified flying objects exert some supreme force on report owners to clean the data.

[@nikahafiz1]

i have same issue but such only appears in power bi service (i.e. not in power bi desktop). i'll try to use the method suggested here to see if that can resolve the 'blank'-item in slicer problem in power bi service.

[@ailtonmoreira]

does power bi allow to create a model (tables and relationship) with data from IoT sensors?

[@gulzarahmedbutt7213]

Great Video Patrick! I just wanted to know that If I want to replace this Blank with some kind of word like "Others" or "Unspecified", How can I do that ? Thanks

[@solomonrobert67]

Thanks for this Video Patrick. Have got into this case many a time knowing that the data is bad. But I have a peculiar scenario. I have a Dim Table with professionals and fact table with the hours spent on a specific task. On placing this data on a table, It gets aligned accordingly leaving some hours under blank. These are hours spent by members who aren't a part of my team but still helped in our team work. Can we group them as "Others" instead of showing a blank field with hours against it?

[@andyparkerson]

In PowerQuery you can join the dim.team table to the fact.time table and pull out just the associated names. This will let you know which facts are not associated with your team members. Then create a column named 'new_member_id' that is member_id if the member is in your team or 1234 if the member is not. Make sure that 1234 is not the member_id of any of your team members. Then throw away the member_id column and use the new_member_id column you just created as the member_id column. Furthermore, create a new table with a single row with member_id 1234 and name "Others". Then append that table to the dim.team table. Now you will have a dim.team table that consists of all of your team members, along with a single row for "Others". You will also have a fact.time table that has foreign keys for dim.team for everybody. There will be no blanks, because anybody that wasn't associated before got swept up in the "Others".

[@solomonrobert67]

@@andyparkerson Thank you so much for the detailed response. Will try this approach and see if it works for me basis the model I have in place.

[@dergimorka6126]

ALWAYS INJOYING WATCHING YOUR VEDIOS YOU know why? because u asked questions and answer ur self

[@PowerBITrujillo]

Great video! =)

[@pierocampilii5182]

Man... You're the man... Now, how can I do that search you did on 1:39 on Excel...? I'm having this issue but in Power Pivot. Thank you in advance

[@govindkulkarni1584]

Hey Patrick, Hope you are well. I am trying out a new thing to refresh Pbi datasets using ssis, powershell and sql control table. However, its a bit challenging to login to power bi service using powershell passing custom params. Can you make it look simple for us. I am using cmdlets as no app registration is required working with powershell. TIA

[@balakrishna-ju8zf]

We can eliminate the blank in report level filter. I have encountered the same problem and I had overcome with above as solution. Any suggestions do let me know

[@priyankaduggal]

@Guy in a cube:- my que getting unanswered in last 3 live sessions.But I really want to know way to pass inputs to power bi in published version.

[@harman210108]

Hi,I need one help,How to change the sentiment color in waterfall chart.is there any way.really appreciate !!!

[@SteveClaes]

We also have blanks but it seems that we don't have bad data (All keys have a matching row in the dimension table). Are Blanks standard when using DirectQuery?

[@lorduakiti]

How to remove (blank) for card sum values column?

[@sergiod8189]

Hi people, does anyone know if there is a way to add the missing value in the dimensional table using dax? Like Addrows instead of Addcolumns?

[@kulchandraneupane7394]

Isn't it inner join is good fix for this?

[@harold5534]

Thanks

[@GuyInACube]

Most welcome! 👊

[@harold5534]

Guy in a Cube 🤜🤛

[@jeffrey6124]

Hi! Patrick hope you could help me on my case, I have a similar problem of values do not display the correct numbers/total after selecting a criteria instead it displays zero and the difference of the file is I'm using is a single report from a combination of 3 sql server database using script in the advance setting while connecting to SQL Server. The error is only for one particular selection in the Slicer I'm using. Appreciate any help. Thank you

[@juanignacioXI]

Cool!, Patrick r u using the Excel file in Sharepoint?, Onedrive? or Teams? or just in ur desktop?

[@GuyInACube]

For this demo, it was just a local file. Nothing special.

[@amitshukla189]

Now we know the reason for blank. I used to just filter to blank but I can fix them now.

[@CanonCrested]

Same here.

[@kishoreyc]

Me too........

[@ZachRenwickData]

If you filter blank, you could be artificially limiting your results. For example, if customer 4 in this video example had sales of $100 and you removed BLANK, you'd not be counting that $100 in your visuals

[@amitshukla189]

@@ZachRenwickData yes I was not aware about this earlier. But thanks to Patrick now I know how to fix this.

[@priyankaduggal]

I too used to filter this out 😂

[@JessicaWilconson-ve6ts]

Hi Patrick, what if our data needs to have blanks?

[@shyamthakur9799]

Hi Patrick, Your vdo's are really osum & it helpful. Could you please make vdo on new pipeline feature on power bi services. Cheers ☺️

[@andyparkerson]

Adam did one last week: kzbin.info/www/bejne/gl7VeKh8eLShZ5o

[@GuyInACube]

We already did 😎 Andy shared the link. Appreciate that Andy 👊

[@shyamthakur9799]

@@GuyInACube opps...I am late...✌️

[@hugarohugaro]

Hi Patrick, quick question. What if the slicer is showing (blank) because intentionally it is linked to a table that contains a subset of the other table is linked to, is there a way to provide a value instead of blank?

[@vzarkova]

Yeeeeesss!!! Hate when blank happens! 😅

[@GuyInACube]

Right there with you 👊

[@stefanstoyanov8157]

Hi, Patrick. Thanks for the great video. I have a question which is again related to blank values. But the issue is different. I couldn't find an answer after browsing for it. Here is the problem: I have a column with three values: True, False and Blanks. I am using this column in a filter in the Filters Pane in the "Filters on this visual" section to filter a specific visual. However, when I select only the True and False values, the blank values are not filtered out, and the number on my visual doesn't change. When I use the column in a slicer, the slicer filters out the blanks, and the number in my visual is correct. I would be thrilled and grateful if you could explain "what the fridge" is going on :D and how to solve it without creating a measure (if possible). I really appreciate any help you can provide. :)

[@kothadurgaprasad5318]

Hii sir, How I will fix the blanks if I am using SharePoint as backend?? And I shouldn't add any data because it's live application

[@rhonskibeat1]

The challenge is when you are querying data directly from Analysis services and joining tables from different cubes and you have data from one table that does not exist in a other table. Access allows you to do a left join only but how do you do that in powerBi or powerpivot? Can you provide insight how to fix blanks when that happens?

[@Mike-yz7dv]

The explanation is not covering other scenarios. Actually, I'm working on one table and a blank exists on years field. In my case, there is no issue with the data model or relationships.

[@square007tube]

I like the Intro music..

[@GuyInACube]

Appreciate that :) 👊

[@hemanthkumar5166]

Hey Patrick, you can't go for fact and update record where transaction there isn't transaction. I would suggest since it is using left join make inner join with both direction. What do u say?

[@GuyInACube]

You mean a bi-directional relationship? I wouldn't jump to that.

[@saradag6939]

If in tat coloumn contains thousands of rows and xontain 100 blank value tn what could we do to replace blank

[@nikakalichava8012]

I actually edit this comment. Thank you for the video I checked all of my data and yes this helped me. Wish you best!

[@younisarrabi190]

Hi patrik I have one question Have colunm with items IDs and want to create card which display count of items that repeated 50 times and more I try many ways but could not success

[@thomasivarsson2468]

Usually called late arriving dimension members.

[@GuyInACube]

Nice. I always try to be early 😎

[@thomasivarsson2468]

@@GuyInACube If you do nothing and accept latency of some dimension members the problem will solve itself on the next processing of the dimension.

[@glenthompson4135]

I have a blank because my data is grouped by weekly periods but the data had future dates in it that were not required so I tried a DAX filter to DISTINCT + SELECTEDCOLUMNS + FILTER exclude future dates, so curious to know if you can remove the blank in this scenario. So I need a programmatic means to solve this if you can suggest.

[@GuyInACube]

Interesting. Depending on how you are filtering/grouping the data it may be unavoidable. Especially with some DAX Functions. You may be forced to filter out blanks at that point.

[@marce_f]

I love more you saying "guy in a cube" than the video ahahaha

[@realitychecktime101]

What would be the best way to solve for this, if the source data, where your getting your dimensional data, cannot be updated, and you need to solve for it in PBI.

[@nishamahmed7081]

I have a column named "status" where only two values will appear "open"/"close". I am using a slicer with this column. There are times I don't have any "close" item in the dataset. Now the problem is that in the slicer where it used to have "close" now shows a "(blank)" text. How can I avoid this blank but write "close" in the slicer rather so that when I click "close" it still filters although it has got no data to show.

[@andrewcragg420]

But which is better - blank, null or 0?

[@gawlianilnrayan]

Patric why there is no sync between two slicers?. eg. if we select one value from one slicer and based on that value another slicer is refreshing but last selected value is always selected and then if we select next value then that selected value disappears. please help patric

[@amazinglife3485]

What if we have a millions rows of data can we replace that blank with some value like others or NA