Blank values in date slicer

[mr_oli]

Hey guys,

I am trying to add one more slicer into Power Bi Report with connection of Year - Month.

I created new column in Calendar table:

And add slicer with that column.

The problem is that in Year - Month slicer I am able to select dates without entries in main table. For example year 1899 which is not visible in year slicer.

I used same filters like in year or month slicer but in slicer Year - Month its not working.

What is the problem with that?

[v-xiaoyan-msft · Accepted solution]

Hi，@mr_oli 

Please make sure your Date table cover all dates in the date field，and there are no null / blanks present in the date field.

Also check you don’t have different Date Time values in the field.

For this situation ，the solution was that the join to the Date table was to a date time field. Joining to a date only field may resolved the issue.

Hope it helps.

Best Regards,
Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Rice1]

this worked for me, thank you!!

[negi007]

@mr_oli I guess one side of your table relationship does not have matching value that is why blank is appearing in your slicer. You may refer to below blog for help

https://www.encorebusiness.com/blog/blank-value-in-the-power-bi-slicer-and-filter/#:~:text=In%20actu....

[Abubu]

That blog post at Encore Business was the only place I found that explained the issue simply and had recommended steps to find the bad data and solve it.  Thank you 🙂

[timg]

Hi Mr_Oli, This could be due to the filter direction. If you currently have the filter direction set to one direction (from your date table to the table you wish to filter) it would mean that the year-month column will not be filtered based on the content in the related table. So it will show all values that exist in the date table. You could change the filter direction to "both" for this relationship to filter the slicer. Or even better, reduce the date table to contain only the dates that your will require (based on your other slicers I would assume that starting the calendar at 2020 would perhaps already be sufficient). 

before filter direction changefilter direction

after filter direction change

[mr_oli]

I can't change it to 'both' because I have another table after "Date" Table 

[themistoklis]

Hello @mr_oli ,

pre-1900 dates are read as text and not dates ( in Excel) and i think the same thing happens also in PowerBI.

Best to check if this is causing issues with your data

[mr_oli]

No, I do not think that you are right - its just sorting in slicer.