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
banthorpe
Helper I
Helper I

Date table slicer not filtering correctly

OK - so here is a new situation I've come across which is confusing me. 

 

I have a date table created using the following DAX (and marked as a date table):

 

Date =
ADDCOLUMNS (
CALENDAR (DATE(2018,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))
 
 
In my data table, I have a startDate field which is a DateTime so in order to be able to create a relationship between the Date table and my Data table, I created a new colum in my Data table which is a duplicate of the startDate column but formated to just be of Date format (DD/MM/YYYY) (as I need the original startDate column to by full DateTime for other visualizations).
 
I  then created  a many to 1 relationship between the two tables (many is Data, 1 in Date table)
 
Problem : When ALL dates are selected in the slicer, my data is displayed, but when I select a single year for example (for which there is corresponding data), it shows nothing. So something is wrong in the way the relationship is being calculated. Is formatting a new column as date only sufficient or does my new "date" column in my data field need to be explicity stripped of all time data?
 
 
 
5 REPLIES 5
sunilraheja
New Member

Facing the same issue. Created a Date table and linked it to 2 other tables in dataset using date columns. However the data is not filtering correctly. If I choose the full wide slicer, then the data shows up, but otherwise, if I filter it for an year, it does not filter correctly.

Not able to figure out the reason.

I'm having this problem also. It was working, and after doing some other work on the other tables, I'm not sure what I did to break it. Hope someone can figure this one out! 

v-danhe-msft
Employee
Employee

Hi @banthorpe,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered to close this topic?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-danhe-msft
Employee
Employee

Hi @banthorpe,

Based on my test, it could work on my side:

1.PNG

Could you please offer your sample data to have a test?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi, Your calendar start in 01/01/2018. In which range of dates are your data? If start dates are before 2018, you can't see anything! Chiara

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.