filter on import data from ssas

Question

hi,&nbsp;i have a problem that my cube is very very big so i can't import the whole data to PBI (i don't want to connect live because i want to be able to add measures, formatting and stuff)how can i filter the data before i'm importing it? i know i can edit the 'M' but it's after the importing, right? so i can edit the query but it still not on the whole data...&nbsp;is someone have an idea to solve my problem? thank you very much!

imkef · Answer

This is a bit counterintuitive, but in order to reduce the number of rows/columns to be returned from your Server/cube you have to write the filter-steps in M. In many cases the query will fold back to the server (this is the technical term for letting the server to the pre-selections).&nbsp;In addition to that, M is partially lazy evaluating, so even in "normal" queries, the commands wouldn't be executed as you read the code but once they are needed for the result (and the engine thinks it's best :-) ).&nbsp;However, there are limitations that you can read here.If you want to pass filter-tables to your&nbsp;SSAS model, you should have&nbsp;a look here (also including the syntax on how to pass simple filter arguments&nbsp;into your MDX-statements).More useful info about querying SSAS-cubes from Chris Webb here.

birdie29 · Answer

Hi Anonymous&nbsp;I've just seen your comment on the OP's issue and thought this looked like a great solution, however when I try this the filters are removed when adding fields so it doesn't make any impact. The reason being is that when clicking the 'cog' you're effectively going back a step to the pre-filter view.&nbsp;Am I missing something?&nbsp;Thank youChris

andytrezise · Answer

Hi&nbsp;I wondered if you found a solution to your problem?&nbsp;I have the same issue...i need to filter data from the SSAS cube at source as there are far too many rows to pull into PBI

anonymous · Answer

As is, this procedure is not really correct. It's flawed yes.

You have to import and then filter out data OR as I noticed.

You can import small amount of data, filter it and then add the remaining columns or dimensions continuing to filter as you go.

It's messy, but effective.

On another note.

PBI team: Please provide pre-filters for this? :)

mp_123 · Answer

Anonymousthanks!&nbsp;do you mean by "table.selectRows"?because it doesnt work for me...&nbsp;

Forum Discussion

filter on import data from ssas

9 Replies

Related content

Filtering Data

imported data not refreshshing in visuals

Filter data before import

Import USDA PSD DATA BASE

filter data on import