Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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?
 
 
 
Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    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
  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous,

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

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

     

    Regards,

    Daniel He

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous,

    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

  • sunilraheja's avatar
    sunilraheja
    New Member
    7 years ago

    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.

    • lbudack's avatar
      lbudack
      Icon for Advocate III rankAdvocate III
      6 years ago

      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!