Date Filtering in PowerPivot 2012 when you put pivot table in excel

Question

Hi

I know this issue clearly and I know it submitted in Connect, and they said it is fixed now, but from what I saw nothing fixed in 2012 (denali), because the option to mark the table as date table won't work for most of the scenarios, I still have date columns which is not unique, and I want to leverage date filtering for those columns in excel, but the date still treated as text, please someone redirect me to something if it is fixed, do I need to install a new version of ADOMD.NET if available?

Answer 1

Hi Mohammed,

There is no such kind of issue at 2012 (denali), please make sure the column's datatype is Date after you importing from other datasource in PowerPivot Windows, then you can filter the unique value at Pivottable on the Excel workbook.

If the issue still exist, please upload your Excel file at https://skydrive.live.com/, I want to have a check to it and then give you a reply.

Thanks,
Challen Fu

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

I don't want to filter the unique value, I want the full date filter in the pivot table in excel. as for the excel sheet check the attached one, I only droped one field in the pivot table, try to filter the date values using the full featured date filter, it only shows "Label Filter" and "Value Filter"

 

Check the attached workbook I created two sheets

• From Data Sheet ( data taken directly from excel, it support the date filtering with no issues)
• From PowerPivot ( data taken from powerpivot, it doesn't support the date filtering and it treat the date as a text)

Note that this is just a quick sample, in my full sheet I have multiple date columns and they contain duplicate values, so I cannot apply this "Date Table" to it.

https://skydrive.live.com/redir.aspx?cid=2992955bbbcadfd5&resid=2992955BBBCADFD5!111&parid=2992955BBBCADFD5!105&authkey=!AP5l_x4UYvhe_i0

Answer 3

any ideas guys?

Answer 4

Hi Mohammed,

I get your meaning after open your file, in the excel workbook, there is a Date Filter on the date type column, but in the Pivottable of PowerPivot, there are two "Label Filter" and "Value Filter" without Date Filter, right? If I misunderstand you, please feel free to let us know.

Actually, even if the type of the column in the Pivottable is Date, there is no Date Filter, there are only two "Label Filter" and "Value Filter", this is by design, you can leverage this two filter to filter what you want to display.

If you want to add this feature, here I still would recommend you to submit a feedback to Microsoft Connect at this link at this link https://connect.microsoft.com/SQLServer/Feedback This connect site is a connection point between you and Microsoft. Your feedback enables Microsoft to make software and services the best that they can be, Microsoft might consider adding this feature in the following release after official confirmation.

Regards,
Challen Fu

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 5

Thanks for your response, you are right regarding what you understand, but note that the first sheet is a PivotTable from normal excel sheet date, it is not a normal table in excel, but it contains a date filter.

You  said submit this to connect, but it is already there and they said they fixed it in denali, but I don't see anything fixed

take a look at the following link, http://connect.microsoft.com/SQLServer/feedback/details/637912/powerpivot-treating-date-columns-as-text

The guy in the connect post was clearly talking about the filter in excel not in powerpivot, but the people from my microsoft they linked it to a post talking about data type change during import from analysis services to powerpivot, which is irrelevant.