Analysis Services - date/integer backed member filters

[xsgao_pbi]

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?

[mwegener · Accepted solution]

Hi @xsgao_pbi ,

so your settings are like in this post?

https://community.powerbi.com/t5/Desktop/Date-slicer-filter-doesn-t-work-with-dates-from-SSAS-Cube/t...

[mwegener]

Hi @xsgao_pbi ,

has your question been answered?

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[mwegener]

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]

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]

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]

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.

[dax · Accepted solution]

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 · Accepted solution]

Hi @xsgao_pbi ,

so your settings are like in this post?

https://community.powerbi.com/t5/Desktop/Date-slicer-filter-doesn-t-work-with-dates-from-SSAS-Cube/t...