Forum Discussion

xsgao_pbi's avatar
xsgao_pbi
Frequent Visitor
6 years ago
Solved

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....
Need Help
  • dax's avatar
    dax
    6 years ago

    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 Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mwegener's avatar
mwegener
Icon for Most Valuable Professional rankMost Valuable Professional
6 years ago

Hi xsgao_pbi ,

 

I don't know your ssas toturial database, but if the columns have the correct data type, the filters should be available.

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

xsgao_pbi's avatar
xsgao_pbi
Frequent Visitor
6 years ago
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]
GO
 
And 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, 2010
 
In 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.
  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional
    6 years ago

    Hi xsgao_pbi ,

     

    try to create a separate Attribute in the Dimension witch based on the Date Column.

     

    If I answered your question, please mark my post as solution, this will also help others.

    Please give Kudos for support.

    • xsgao_pbi's avatar
      xsgao_pbi
      Frequent Visitor
      6 years ago

      I see your point of testing a date column backed attribute. But I did not create this SSAS database so not sure where to add one.

       

      But in the existing case, it's an integer column backed attribute (key is integer, value is date) and I cannot see integer comparison operators. So I believe to create another attribute that is based on Date column won't give me the date comparison operators in Power BI.