Filtering SQL Server Analysis Services database in import mode

[pratafran]

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!

[mwegener]

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

[pratafran]

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")

[mwegener]

Hi @pratafran,

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

[pratafran · Accepted solution]

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