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
markus_zhang
Advocate III
Advocate III

Slicer shows values in between

Hi experts,

This is kind of strange, I have a date table with a list of dates, like:

2018-10-17

2018-10-24

...

But if I put a slicer on it, the slicer will give me ALL available dates, which is not reasonable, why would I want values NOT in the column?

 

This is just a date table and is not in the data model, so there is no relationship.

I searched but seems no one had this issue before, so I'm wondering if I can set it up somewhere?

Thanks in advance.

1 ACCEPTED SOLUTION

@markus_zhang

 

I thought so. I've come across other types of problems cause by this. It is always better to create your own date tables. Additionally, if you don't mark them as date table Power BI Desktop will create its own date stuff in the background  and will cause problems sooner or later. Annoying problems that do not seem to make  any sense like what you were describing.  

View solution in original post

5 REPLIES 5
AlB
Super User
Super User

Hi @markus_zhang

 

Can you share the pbix? If necessary a version without confidential data that reproduces the problem? 

An additional question, sorry, maybe I should use another post but I think it's related.

 

Say I have a column that contains dates from 2018-10-17 to 2019-01-16, and I put it into a Matrix, why does Power BI automatically expands the dates to full year of 2018 and 2019? I feel like it's the same issue, and this time it's even worse because I can't filter out those unneccesary dates prior to 2018-10-17 and post to 2019-01-16.

 

I must did it the wrong way I guess, becasue it's such a basic functionality.

 

FIGURED OUT!

Actually I need to set it as a date table and suddenly all other dates are gone! Such unnatural requirement... I just don't get the meaning of it...who on earth would need this anti-feature?

@markus_zhang

 

I thought so. I've come across other types of problems cause by this. It is always better to create your own date tables. Additionally, if you don't mark them as date table Power BI Desktop will create its own date stuff in the background  and will cause problems sooner or later. Annoying problems that do not seem to make  any sense like what you were describing.  

@markus_zhang

 

And something else: it's also best to disable the Auto Date/Time option, of whose existence I had no clue until earlier today. Check out the odd behavior I came across recently and its solution in this post

Hi @AIB thanks for the response. It does contain sensitive info so I can't share it.

But I think it's just the same for every PBIX file, no?

Basically I just created an Excel file and loaded the date column, here is the link:

 

https://www.dropbox.com/s/vmp7zp8venswkjc/Date.xlsb?dl=0

 

Then I tried to put the Date column on a slicer, and it just shows all days in between the values. I gave up and create a date column that has continous dates so I guess that's it.

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.