Forum Discussion

sanman's avatar
sanman
Helper I
6 years ago
Solved

Issue using date slicer from dynamic table

For sake of simplicity here's my scenario:

I have a table Sales with entrydate column. It contains data from 2017 to 2020.

I wanted to use the power of DAX Date Table so created a new DateTable from year 2000 to 2030 and connected the date fields in the two table.

Now I want to put a slicer so I use the new DateTable slicer to filter with year, but the slicer is showing years 2000 to 2030 (from the date table instead of only showing 2017 to 2020). 

How do i have the slicer only show me the valid years instead of all years in the dynamic table?

Need Help
  • DataZoe's avatar
    DataZoe
    6 years ago

    If you click on the date slicer, you can add a field from the sales table or a measure to the "filters on this visual" part of the filters pane. Then filter it to all "is not blank".  You can also hide that from the end users when it's published.

4 Replies

  • DataZoe's avatar
    DataZoe
    Microsoft Employee
    6 years ago

    If you click on the date slicer, you can add a field from the sales table or a measure to the "filters on this visual" part of the filters pane. Then filter it to all "is not blank".  You can also hide that from the end users when it's published.

    • sanman's avatar
      sanman
      Helper I
      6 years ago

      Wow that worked DataZoe. Thanks!
      That now brings me to another issue 🙂

      For sake of having a nice Dashboard, I wanted to present different data into the same dashboard so I added another table from another datasource with a date field - which I connected to the Dax dateTable. I want to use the same slicer to filter for the year selected for both the original Sales Table visual and the new Table visuals - but as soon as I connected the date field from New Table to the Dax DateTable, now the slicer is showing me all the years ie from 2000 to 2030 again!

      • DataZoe's avatar
        DataZoe
        Microsoft Employee
        6 years ago

        sanman that is interesting! It would depend on which field you did use to filter the date slicer, what was the date range of the new data, and what type of relationships are between all the tables.

         

        I should mention another method to do it is to create a both directions relationship between Sales and Date tables. Sometimes this excludes more data than expected and is not an ideal solution.