Forum Discussion
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!
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")
Hi pratafran,
I mean use the data source of the Analysis Services database and not the Analysis Service database as the source.
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!