Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
xsgao_pbi
Frequent Visitor

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?

2 ACCEPTED SOLUTIONS

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...

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

dax
Community Support
Community Support

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.

View solution in original post

7 REPLIES 7

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


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.

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


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
Community Support
Community Support

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.

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...

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.