Forum Discussion
Analysis Services - date/integer backed member filters
I am connecting to SSAS Tutorial database. After draging a date backed (date column as key) member into filter and selecting advanced fitler, I still see string operators (starts with, contains, etc.). Is that possible to use date comparison operators (before, after, between, etc.) to filter date members? Also, can I use numeric comparison operators (less than, between, etc.) to filter integer backed members?
Hi xsgao_pbi,
It seems that you are using Multiple-dimension datasource, right? If so, when you use import model, it will consider it as text or int, so you need to change its type in powerbi by modeling. If you use live connection, it should have column like date key.value which is your value column in database and it should be date, you could use this in slicer.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
- This is the table in SQL Server database:CREATE TABLE [dbo].[Date](
[DateKey] [int] NOT NULL,
[Date] [date] NOT NULL,
[SimpleDate] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Date] PRIMARY KEY CLUSTERED ( [DateKey] ASC )
) ON [PRIMARY]
GOAnd some sample data:DateKey Date SimpleDate
20100101 2010-01-01 Jan 01, 2010
20100102 2010-01-02 Jan 02, 2010
20100103 2010-01-03 Jan 03, 2010In SQL Server Data Tools, create a project and link Date table to Date dimension, and set KeyColumns to Date.DateKey, NameColumn to Date.SimpleDate, ValueColumn to Date.Date. And deploy changes to SSAS.Back to Power BI, I can not filter Date members using date or integer comparison operators, all I see are string comparison operators which means I can only filter on NameColumn.
