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
Anonymous
Not applicable

how to remove the (blank) or null from slicer?

the column have null value, after I replace the NULL value, in the slicer still show the "(blank)", How can I remove this from slicer?

Thank you

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@Anonymous 

1) convert the Slicer to a Table

2) go to the Visual Level Filters

3) select is not blank

4) convert back to Slicer

Let me know if this works - as its been a while since I did this last.

View solution in original post

14 REPLIES 14
Arkaplan86
Frequent Visitor

1) Go to country and click the menu which is the right side of the country.

2) Pick add filter 

3) Select ıtems you want to add by page level filters

4) Go to slicer..

 

I did like this, It is working..

it worked for me as well


@Arkaplan86 wrote:

1) Go to country and click the menu which is the right side of the country.

2) Pick add filter 

3) Select ıtems you want to add by page level filters

4) Go to slicer..

 

I did like this, It is working..


 

If the range / size of your dataset is set to greater than the no. of rows you are bringing back with your refreshed data , then you will get blanks or zeroes in the additional rows, and the pivot table and its slicer will show this. 

Instead of leaving these unpopulated rows as blank or null, set them to one of the values in the populated rows.

 

eg: if the returned 'Year' column is one of 2020, 2021,2022 create an additional formula field column 'Year2' equal to 'Year' with the condition that if the row is unpopulated, instead of leaving as blanks or zero, set to one of the values in the populated rows eg: 2020, or 2021 etc. 

 

Then incorporate this new calculated field 'Year2' in your Pivot table instead of the original one, and create your slicer against this.

Only the populated values will be returned.

There will be no impact on your analyses because there is no additional data in the other columns.

 

Do this for all slicers which are returning a blank that you can't get rid of.

Arkaplan86
Frequent Visitor

1) Go to country and click the menu which is the right side of the country.

2) Pick add filter 

3) Select ıtems you want to add by page level filters

4) Go to slicer..

 

I did like this, It is working..

TobiasF
Frequent Visitor

I had this exact problem and the fix didn't work for me with the native slicer or tables. 

In my case the values for the table is a mix of IF statements and dax. So in my case I simply tweaked my IF statement so that the else case of the if set the blank rows to null. Then I simply when to the filter as described and selected Advanced, IS NOT and manually entered null. 

It don't have to be null you could type in REMOVE ME etc. Basically the IF case created a flag and I use the filter to filter the table so IS NOT     flag.

I hope this helps. 

asocorro
Skilled Sharer
Skilled Sharer

The HierarchySlicer custom visual does not have this problem and may be used as a replacement for the built-in Slicer.

 

https://app.powerbi.com/visuals/

 

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro

@asocorro Thanks for the info. That doesn't solve my issue completely though as for many scenerio's I don't want to rely on a custom visual as there are still some occassional compatibility issues with different releases.


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

Yes, this happens due to the gaps in data. I was struggling with this and finally cleared all data and all identity columns. In my case I reset identity column to zero and that solved my issue.

Sean
Community Champion
Community Champion

@Anonymous 

1) convert the Slicer to a Table

2) go to the Visual Level Filters

3) select is not blank

4) convert back to Slicer

Let me know if this works - as its been a while since I did this last.

It worked for me, thanks! Version: 2.111.590.0 64-bit (November 2022)

Hi All

 

I have converted the slicer to table and removed blank and changed back to slicer, still the blank value shows, please advise

 

 

Jayanthan

Anonymous
Not applicable

Thank you. it is help me. 

Hi,

 

That trick didn't work for me. Funny thing is, I had blanks initially in a Linked Excel file then removed them. After that I refreshed my data the (Blank) in the slider goes away. If I save the file and reopen, the (Blank) in the slider is back. Tried recreating the table from scratch in BI, didn't make a difference.

@dtartaglia This work around no longer works... which is disappointing from my perspective. There is no way or new functionality yet to replace this. The blanks are more than likely caused by gaps in data between tables.


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

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.