cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

limit SSAS data import through MDX/DAX query

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.

SSAS import data.PNG

 

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

SSAS import data2.PNG  

 

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: limit SSAS data import through MDX/DAX query

Briiliant! Thanks Lin.

Br,

Jayjay0306

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: limit SSAS data import through MDX/DAX query

hi, @jayjay0306 

You should use the script as below:

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

5.JPG

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

Result:

6.JPG

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: limit SSAS data import through MDX/DAX query

Briiliant! Thanks Lin.

Br,

Jayjay0306

View solution in original post

Highlighted
Helper II
Helper II

Re: limit SSAS data import through MDX/DAX query

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

Highlighted
Frequent Visitor

Re: limit SSAS data import through MDX/DAX query

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors