limit SSAS data import through MDX/DAX query

[jayjay0306]

Hi,

I am trying to limit an import data load from a SSAS Tabular cube in Azure to PBI Desktop. As I see it, it is possible in the query load by making a DAX/MDX script.

example: I want to extract a list of customers  - see below - by "Customer ID" and limit the list to the Customer segment "Retail Account" (attribut:"Customer Type").

  

I have tried the following script, but it gives me an error:

FILTER(ALL([Customer ID]);[Customer Type]="Retail Account")

As I see it, this is the only option to limit the amount of data in the dataload/query? All options/best practice are welcome as the information on this subject is quite limited (and no, I do not have edit acces to the SSAS cube).

Can somebody enlighten me on this issue ?

thanks.

Br,

Jakob 

[v-lili6-msft]

hi, @jayjay0306 

You should use the script as below:

evaluate(filter('for DAX', [Customer Type]="Retail Account"))

in my case, 'for DAX' is a table name

Result:

Moreover, you can start with the following blogs about writing MDX or DAX queries.

How to query a SSAS Tabular model database with MDX expressions
How to query a SSAS Tabular model database using DAX functions

Best Regards,

Lin

[pratafran]

Hi @v-lili6-msft 

I found your solution for filtering an import from SSAS in import mode and really solved one of my main current issues.

The thing is that now I'm interested in importing two different tables from my SSAS Tabular model. One is let's say the main Billing Table with a list of Invoices #, Dates and amounts, the second table is the Business Unit for each invoice. They are related by an ID in the Tabular model but it is hidden and not imported in Power BI so I cannot replicate that relation.

If I do the import mode and select the 4 fields from the two different tables, it automatically merge everything in one table and I get the expected result, but the problem is that the amount of data is huge and I want use your "evaluate" function to filter this prior to import but if I use it for filtering the Invoicing table by date, then I'm not able to select columns from other tables so I guess I should find a way of adding the columns from the DAX itself.

Any idea?

Thanks in advance

[Tangi12]

I have the same question.  Filter with two tables.  Did you find a solution?

[jayjay0306 · Accepted solution]

Briiliant! Thanks Lin.

Br,

Jayjay0306