cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Filtering SQL Server Analysis Services database in import mode

Hi everyone,

 

I have data stored in a cube and connected through a Live Connection to Power BI.

For many reasons (adding other sources of data, making power queries, adding calculated columns, etc), I want to create some KPIs connected to this cube but in Import Mode.

Due to the database has historical information, it is huge and of course this impacts both in performance refresh and file size.

 

I see that from the Import Windows, I have the option of adding MDX or DAX code.

 

Is any way of filtering the data in the importing process?.

 

Which should be the code for achiving this imagining two column filters (Billing Date = Last 12 months and Document Type = "INV").

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper II
Helper II

Re: Filtering SQL Server Analysis Services database in import mode

Hi @mwegener 

In that case, I cannot access the database directly.

 

I have found a way of filtering the database using DAX:

 

with this expresion:

evaluate(filter('Table1',[Field1]="INV") && [Invoice_Date]>=20190101))

 

I found in the following link there a similar question and the answer so I consider this topic resolved. Thanks!

https://community.powerbi.com/t5/Desktop/limit-SSAS-data-import-through-MDX-DAX-query/m-p/701065#M33...

 

View solution in original post

4 REPLIES 4
Highlighted
Super User III
Super User III

Re: Filtering SQL Server Analysis Services database in import mode

Hi @pratafran ,

 

Can't you access the DWH directly?
Analysis Services database is a bad source for another Analysis Services database.

 

I am looking forward to this feature:

Composite models over Power BI and Azure Analysis Services datasets

https://docs.microsoft.com/en-us/power-platform-release-plan/2020wave1/business-intelligence/composi...

 

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

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether

Highlighted
Helper II
Helper II

Re: Filtering SQL Server Analysis Services database in import mode

Hi @mwegener 

 

I'm not sure what do you mean by "access the DWH directly". I have the server name and the database so I guess the anwer to your question is yes. I'm connecting through SQL Server Analysis Services Database because basically is the only way I know 🙂 but if there would be a better option, just let me know.

 

The composite models would be the definitive solution to all this problem!, I'm also looking forward to that option.

 

I found a post that partially answers my question

https://forum.enterprisedna.co/t/filter-data-for-import-from-ssas-tabular-model/702/9

 

But I'm not really sure how to adapt it to my model.

Basically, the proposed MDX/Dax query for the import mode is:

 

SELECT NON EMPTY{ [Measures].[Sales], [Measures].[Quantity] } ON COLUMNS, NON EMPTY CROSSJOIN( {[Coutnry].[State].[State]} ,{[Time].[Date].[Date]} ) ON ROWS FROM ( SELECT { [Time].[Date].&[2019-01-01T00:00:00]:[Time].[Date].&[2019-01-31T00:00:00] } ON 0 FROM [Sales] )

 

I'm a little confused about the code. I have just the following data:

Server name = DW982\SSASTAB

Database = Model

Table = Invoicing

Fields to filter => Invoice Date>01/01/2019 & Document_Type="INV" (both from the table "Invoicing")

Highlighted
Super User III
Super User III

Re: Filtering SQL Server Analysis Services database in import mode

Hi @pratafran,

 

I mean use the data source of the Analysis Services database and not the Analysis Service database as the source.

 

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

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether

Highlighted
Helper II
Helper II

Re: Filtering SQL Server Analysis Services database in import mode

Hi @mwegener 

In that case, I cannot access the database directly.

 

I have found a way of filtering the database using DAX:

 

with this expresion:

evaluate(filter('Table1',[Field1]="INV") && [Invoice_Date]>=20190101))

 

I found in the following link there a similar question and the answer so I consider this topic resolved. Thanks!

https://community.powerbi.com/t5/Desktop/limit-SSAS-data-import-through-MDX-DAX-query/m-p/701065#M33...

 

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors