cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GGR
Regular Visitor

SLICERS -> blank value

I have a question about using slicers in my report.

I make slicer which show the two years, I have just two values in my year table.

But I also see blank value , is it possible to hide this one ?  

 

PBI.png

 

Thanks for your time

 

GGR

1 ACCEPTED SOLUTION

@adamgor A work around I use quite often is to start with a visual that allows you to filter the value, select/exclude the blank, then change the visual to a slicer and the blank should be gone.


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

View solution in original post

56 REPLIES 56
BC-AI
New Member

I right-clicked on (Blank) in the visual and clicked "Exclude".

 

The "(Blank)" grouping no longer appeared in my visual which was good enough for my purposes.

Djs231
Regular Visitor

Simple and easy to overcome...and it is already built in...

Create a quick measure from the data entity measure used for your slicer/visual.

 

When creating the Quick measure select "different from the filtered value"

Then add your original entity measure as the default.

Then select the drop down -

options are:

"Show as Blank" 

Or

"Show as "0" in place of blank"

 

Select 

"Show "0" in place of Blank"

 

Now save the quick measure.

 

Go back to your slicer/visual and replace the selected entity measure with your newly created quick measure.... and Voila...No More (Blanks) on your visual/Slicer.

ignas
Advocate II
Advocate II

Hey guys 🙂

 

I am not sure if anybody did not have the solution earlier but I did this:

  • Create 2 filters
  • First filter filters second filter, but nothing else in the page. Then I hide this filter somewhere in a corner with changing colors.
  • Then second filter does not have anymore options that are not selected by the first filter.

 

Regards,

 

Ignas

 

 

Anonymous
Not applicable

Thank you very much, that was the solution for me.

Nishantjain
Continued Contributor
Continued Contributor

I don’t think this is a bug. The reason you may think this is a bug is because the "Slicer" treats "Blanks" different to other visuals. Try the following to see if you have the same issue as I did and then how you can fix it

 

Issue:

Fact Table

 

[Column 1]      

a1

a2

a3

 

Data Table

[Column 1]      [Column 2]    [Column 3]

a1                     b1                   100

a2                     b2                   200

                         b3                   300

 

Relationship 

 

Fact Table [Column 1] >> Data Table[Column 1]

 

Bring [Column 1] from Fact Table into slicer

Bring [Column 1] from Fact Table into table

 

You will see [Blank] in the Slicer although you don't have any blank in this column in the fact table. But, when you bring the same field in the a table visual you don't see the blank. The table visual doesn't show the blank because a table visual (or any other visual) will remove the row if all the values in that row is blank. 

 

The reason for you see Blank in the slicer is because the Data Table has a "blank" value in [Column 1]. 

 

If you bring [Column 1] from the Data table in the "Table" visual, you will not see the blank. This is because the "table" will automatically remove the blanks. Bring in another [Column 2] from Data table in the "table" visual and you can now see the blank.

 

 

HOW TO FIX THIS

 

Create a table from the Data table, bring in the column causing you this issue and another column (both from the Data table) without summarizing any of the field (i..e don't add or count any of the field). You will see the row with the blank field. 

 

Thanks

Nishant

 

 

 

Hi Team,

 

im new to power BI so please bare with me. im working on a performance report for our company but when i created sumary table with Gauge Chart, it shows a blank in the slicer. i checked all my raw data but i cant see any blank in there. please see below for the samples.

 

 

 

actual table.actual table.sample table. just to see if i can see the employee with blank values.sample table. just to see if i can see the employee with blank values.

PwC_Marcel
New Member

In my case the (Blank) on a slicer only appears if there is more than one relationship to the dimension table. So it seems like a bug since I do not have any blanks within my data.

I was able to address this by simply changing the way the table interact with each other.  In the relationships section in Desktop you need to select the relationship and the option on the bottomr right "Cross filter direction" needs to be set to "Both". 
One more bit of advice...if you want to use Row Level Security you cannot check the box "Apply security filter in both directions".  No matter the difference having the Both should address your issue though as long as you do it on the relationship to the table the filter belongs to.

bmk
Frequent Visitor

Thank you for this. My table does not have any blanks but on implementing this option addresses the problem. Would you now happen to know why this eliminates the blank? 

Thank you! You're the best this worked and I was literally losing it over this tiny lil error. My data did not have any blanks and I tried everything from removing blanks, replacing null, etc.

PwC_Marcel
New Member

In my case the (Blank) on a slicer only appears if there is more than one relationship to the dimension table. So it seems like a bug since I do not have any blanks within my data.

chethan
Resolver III
Resolver III

To remove the Blank in slicer

 

create YearTable with Index > build relation YearTable to DateTable

 

Use the YearTable Year to your year Slicer

matemusic
Advocate III
Advocate III

If I have blank value in slicer i move the same column that is in slicer to page or report level filter and then chose to show just non blank values

I also used this trick.

 

2017 08 25 - Page Level Filters.png

 

 

 

 

That seems to work; thank you! 

Dag
Advocate II
Advocate II

This is not solved.

 

A (blank) shows up in the slicer, no matter what data it is connected to. 

It seems lika a bug. The workaround is not helpful (anymore?).

 

 

 

SabineOussi
Skilled Sharer
Skilled Sharer

Did anyone submit this as a ticket?

Though it is not quite an error, but there should be a table relationship related option to choose whether to show blanks or not.

Hi,

 

I have 10 data tables with product information, demand qty and period. those are related with keydata columns created from product and period information.  I used many to one relationsships between the files.
I use several slicers and all of them are showing blanks, while none of the tables containing blanks.

Moreover in the visuals (charts) also there are blanks and the columns in the visual are showing totally wrong numbers compared to source files.

I figured out that the sum of the blank columns and the value columns would be the correct numbers. So, I think, there must be a something with data relations.

this might apply for the slicer blanks as well

 

Untitled.png

tomcat318
Frequent Visitor

I believe the "HierarchySlicer" also removes the blanks automatically, and gives you the option to "show items with no data" at the Fields level should you need the blanks

Anonymous
Not applicable

Hi,

 

I went to investigate and found that the company that developed this (in many ways rather excellent) custom visual still has "ragged hierarchy" (which means that "empties" are not displaid) on their development list. I hope they get to it soon, or that MS makes this visual standard with a few enhancements.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors