How to wirte DAX function to filter data from SSAS for import connection to limit data in Power BI

[Deeintu]

Hello All,

I'm new to Power BI.

I'm trying to import data from SQL server Analysis Services database. Count of records in this model is around 5 million records, how can I get only 200k records by applying filter during import.

Requirement for me is to join two models and produce the output for building a report at the end.

Model 1 has : 200k records

Model 2  has : 25K records

Option1: when I choose (Model 1) with  ConnectLive: it doesn't allow me to join  with the Model 2 because all options are greyed out because of this connection.

Option 2: when I choose (Model 1) with Import: It will alllow me to join with other Model because of this connection but it brings all the data from the datamodel. How can i bring only 200k records instead of 5 millions and later it will be joined with the Model 2.

I'm trying below DAX formula, but getting ony Table2 data from Model 1 and not bringing other tables (Table1 and Table3) in that Model 1 for my analysis

Lets say Model 1 has Table1, Table2, Table3, Table4

But I need to bring only  Table1, Table2 (by adding below filter condtion to bring only 200k records) and table3 .

Filter condition will be something like this but unable to write in DAX:

'Table2', [State]="NSW"  OR [City] in ("Sydney", "Melbourne", "Brisbane")

Expected Output  for Model1 should be:

Table1, only 2 columns required from this table

Table2, all columns with filter condition,

Table3, all coumns

Please let me know how can I achive this?

Thanks in advance

Dee

[amitchandak]

@Deeintu , In Dax we have select columns , summarize, etc to cut now a number of columns we are bringing in

https://docs.microsoft.com/en-us/dax/selectcolumns-function-dax

selectcolumns, filter should do as it can take dax. But you have add every table like a new connection

another option is power query parameters

https://docs.microsoft.com/en-us/power-query/power-query-query-parameters