SSAS - cube filtering on import

Question

I am new to SSAS and after trying for hours to solve this problem I asking here.

I have a msOLAP cube that I want to import into SSAS PowerBI but due to large database I want to pre-filter it befor importing. The cube has measures in cpe_fact table and many other dimensions i.e. dim_time, dim_product, dim_material etc...

What I am trying to achieve is getting all the fields from the fact table joined with a subset of dimensions (i.e. only dim_time and dim_product) and filter them by date (i.e. cpe_fact.sale_date < now-6 month)

I tried to put this as MDX query but could not get any data by usin this MDX:

SELECT  
    { [CPE_FACT].[MAIN].[SALES_Q]} ON COLUMNS,  
    { [Selected_Date].[POSTING_DATE] } ON ROWS  
FROM [CPE_Analytics]

I get this error: cube either does not exist or has not been processed evewn before I had a chance to define the WHERE part.

I tried DAX :

evaluate(filter('CPE_FACT', [AGENT] >= "26003"))

it worked but only for CPE_FACT table but i didnt understant how to join with the other dimensions...

My Question: How can I import some Facts Join Few dimentions from the Cube? Example SSAS Connection -

It would be best not to import it from the cube, but load it from the tables/views the Multi Dimension cube sit on. Power BI can connect to an MDX cube in Live connection mode, no importing required — Jon

BOUBRIT Nacim BOUBRIT Nacim · Accepted Answer · 2020-11-04T15:51:35

Instead of using an MDX/DAX query use Power Query editor in two steps :

chose the tables you want to import (cpe_fact,dim_time and dim_product).
apply a filter on the date column in the fact table (cpe_fact) to load the desired resultes.

visit : https://radacad.com/only-get-the-last-few-periods-of-data-into-power-bi-using-power-query-filtering

SSAS - cube filtering on import

1 Answers