I am writing this quick blog post seeking recommendations for a problem I have encountered with date fields using the Analysis Services data connector in Power BI Desktop. This post is long overdue and any help would be greatly appreciated!
In my organization we use various multidimensional models, generally consumed through Excel pivot tables, for our self-serve business intelligence. Up to this point, I have tried to use these cubes as a data source for my Power BI reports for the sake of consistency with the rest of my team. It has also proved to be a great learning experience for me, as I have not previously utilized Analysis Services as a data source in Power BI.
I will walk through my process of connecting to Analysis Services and where I encountered the error. I started with extracting data from one of our cubes to add to one of my existing reports. This required me to import the data from the cube. I added the fields I wanted to my model.
I wanted to manipulate and transform my data. Specifically, I wanted to filter by date. I was importing transaction data and wanted to limit the records to only the past year. However, when I loaded my data into the Query Editor my date field came through as text data type.
I simply changed the field data type to a date to fix this and everything seemed to be working OK.
I continued with various filtering without issue. Then, when I tried a group by transformation, I encountered the following issue:
DataFormat.Error: We couldn’t parse the input provided as a Date value.
Details:
Unknown
If I change the data type as my last applied step, there are no issues and I can use date intelligence visualizations and filters. The error only seems to occur if I change the data type and then continue to try additional transformations. I have also experienced this issue when connecting to Analysis Services through Excel 2016, which has resulted in us not being able to use the fantastic date slicers for our reports. I have yet to find an explicit solution to this error. This is frustrating as I often want to initially filter my data set by date range.
I have tried using text or member filters on my text type date field to limit the date ranges, but this is obviously not an efficient or exact solution, particularly as I am looking to leverage date intelligence filtering for my rolling one year time period.
My current solution is to connect to the database view used as the source for this cube. Everything works fine connecting through the SQL Server database connector. I do not know if this error is caused by how I am extracting the data from the cube, an issue with the cube itself, or if this is a bug. If anyone can offer any advice to fix my process, logic, or manipulation steps it would be greatly appreciated!