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 IV
Super User IV

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
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

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 IV
Super User IV

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
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

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

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors