Blank values in slicers

[zivhimmel]

Hi,

Blank values started showing up in my slicers for no reason.

I don't have blank values in the table.

If I refresh the data(in Power BI Desktop) it disapears. Then at some point comes back again.

Any idea why?

Thanks.

[lotussutol]

I have a query that I tell it to get sysdate-2 days of data.

I have a filter on some graphs that have a relative date span to show the last 9 days of data.

Somehow it thinks there are blanks due to the fact that the filter dates include the -2 days of data I chopped off. That sounds confusing.

this might make more sense. Today is 9/21/2018. so my data with sysdate-2 does not have data for 9/20 or 9/21.

On a graph my filter that says show the last 9 days of data creates a date span of 9/13 to 9/21. 

Somehow that causes (BLANK) to show up in my slicers even though there is no blank data.

I remove the -2 from my query and the (BLANK) in my slicers goes away.

I know this is an old thread but I found it trying to find an answer.

[zivhimmel]

Probably won't help you much, but if your data is filtered to show last two days, why filter the chart to show last 9 days?

[lotussutol]

Hi, sorry.

I meant to say that my data is first of year to sysdate-2.

My filter on a graph is set to show the last 9 days and I believe the two days of data that I exclude is what is causing the (Blank) to show up on my slicer menu.

[Sean]

@zivhimmel

To solve this right away - switch to the Hierarchy Slicer

To find out why/when it happens read here - specifically @OwenAuger's post on page 2

http://community.powerbi.com/t5/Desktop/SLICERS-gt-blank-value/td-p/6726/page/2

(For me it happens in a bit different scenario than the example given by Owen - mine involves 4 tables but same principle)

The way I resolved it in my case (Note - I don't know if this would make sense/apply in yours)

I created a new COLUMN in the "many" table - use RELATED ( One SideTable[SlicerColumn] )

to bring to the "many" table only the values you need - and then use this new column instead for your Slicer

Again please note that this works in my case which is slightly different than Owen's example because

selecting Blank in my slicer results in all charts measures visuals showing nothing

unlike in Owen's example which actually if I recall displays a value when blank was selected in the slicer!

(hope this makes sense)

Good Luck!

[zivhimmel]

Thanks @Sean.

I actually have no blanks, unlike the case in the link, and there are no cases of the tables relating to the date dimension holding values that don't exist in the dimension.

I cannot switch to the hierarchy slicer because it looks different and my users are already happy with the existing design.

I think we'll just manage with the blanks for now 😞

But thanks for taking the time to answer me.

[Phil_Seamark]

This doesn't solve your root cause, but you can set the visual filter on your slicer to never include blanks.

Would be good to know why they are there though.  It suggests you do have blanks.  How many rows in your table?

[zivhimmel]

Hi @Phil_Seamark, thanks allot for your answer.

I have no blanks, and there's no option to filter out values on a slicer.