Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Single Date Slicer for Multiple Dates on Visual

Hello!   I have one table that contains 9 columns, 7 of which are dates. They are dates of an enrolment process. I need to create a visual (bar chart) that shows the count of the dates for each col...
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous this is what you need to do, which will take care of everything.

     

    Unpivot your table, in query editor, select your school column and right click, and select upivot other columns

     

    It will add two column, one attribute which will be your category and other column value which will be your dates

     

    Apply the changes, add calendar dimension in your model (many blog posts are there).

     

    On bar chart, use date/month/year on x-axis from calendar table, legend will be your attribute and value will be count of rows from your table.

     

    Now you can slicer and dice the data by date or by category.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

sounds like you need to related a date/time table for each of your dates and then a master date/time table related to each one of those date/time tables.  Then you set your slicer on the month field of the master time table and use that month in the visualization.

 

Wouldn't that work?

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

Anonymous this is what you need to do, which will take care of everything.

 

Unpivot your table, in query editor, select your school column and right click, and select upivot other columns

 

It will add two column, one attribute which will be your category and other column value which will be your dates

 

Apply the changes, add calendar dimension in your model (many blog posts are there).

 

On bar chart, use date/month/year on x-axis from calendar table, legend will be your attribute and value will be count of rows from your table.

 

Now you can slicer and dice the data by date or by category.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks very much! This seems to be doing the trick!

     

    I do have an error message for two of the columns though:

     

    DataSource.Error: Microsoft SQL: The type of column "txtWithdrawnDate" conflicts with the type of other columns specified in the UNPIVOT list.
    Details:
    DataSourceKind=SQL
    DataSourcePath=xxx
    Message=The type of column "txtWithdrawnDate" conflicts with the type of other columns specified in the UNPIVOT list.
    Number=8167
    Class=16

     

    I have checked that the column type is Date/Time which it is. Do you know how I could solve this?


    Many thanks, much appreciated!