Pre-Filtering Data Before Importing

[akim1]

The data that I'm trying to import from DB is too big and my desktop can't handle the import. I'm using SQL Server Analysis Services DB as a source DB.

Is there any way that I can filter (or select certain fields) before I perform import process? I see MDX or DAX query option but I've never used this function before.  I'm trying to filter a "Company Code" column from a "Company" table where the value is "OT110".

[grazitti_sapna]

Hey you can achieve this by using DAX

Measure = FILTER(TABLENAME, TABLENAME[DATE] <= EOMONTH (TODAY(),0) Here today() will return current date and EOMONTH (TODAY(),0 will return end of current month in accordance to the data

I hope this will help.

[Anomoyes]

hi if I have to filter the date is on or before end of current month, each month dynamic for eg this month is may so till 31 may... Which dax is helpful

[Thejeswar]

@Anomoyes ,

Your requirement is not clear as to what you require

But from what I am understanding posting this reply

1. If you want to apply a filter before importing the data for the last day of the current month, refer the DAX below where I have shown the same

EVALUATE
SUMMARIZECOLUMNS (
'Table1'[Column1],
'Table2'[Column1],
'Table2'[Column2],
'Table2'[Column3],
'Table3'[Column1],
FILTER ('CALENDAR','CALENDAR'[DATE] <= EOMONTH(TODAY(),0)),
"Measure1",[Measure]
)

2. If you want to create a column that dynamically gives the last day of each month

LastDay = EOMONTH('Table'[Date].[Date], 0)

3. If you want to create a measure that should return the data that is less than the last of the current month by default

a. Create a measure to get the Lastday if you already don't have it

LastDay = EOMONTH(TODAY(),0)

 b. Create another measure as given below to get the sum of value

Sales until current month = CALCULATE(SUM('Table'[Sales]), 'Table'[Date] <= MAX('Table'[LastDay]))

Change the column names as required

Regards,

[akim1]

@Anonymous  Thanks for the quick reply.

Without the DAX query, when I select multimple columns in different tables (default clicking function in PowerBI), the output data is automatically joined in the backend then the flattened data is loaded to Power Query Editor.  When I used your statement, it seems it just returns the value from the Company table and the join doesn't happen. 

Does this mean that I have to write all the join statments in DAX to filter just one column?

I'm using SSAS as a data source. 

[Thejeswar]

@akim1 ,

Yes you will have to write a DAX that will select the columns, apply the filters and define the measures if you want to bring in all data at once. This will bring in a flat table with all the columns that you have requested as the joins are already defined in the SSAS Cube

In this case, DAX syntax will be as follows

EVALUATE
SUMMARIZECOLUMNS (
'Table1'[Column1],
'Table2'[Column1],
'Table2'[Column2],
'Table2'[Column3],
'Table3'[Column1],
FILTER ('Table3','Table3'[Column1] = "Value1"),
FILTER ('Table2','Table2'[Column1] = "ValueA"),
FILTER ('Table2','Table2'[Column2] IN {"Value1","Value2"}),
FILTER ('Table2','Table2'[Column3] IN {"Value3","Value4"}),
"Measure1",[Measure]
)

The Other Option would be to import the Company table by using DAX. The Other tables need to be imported separately using selecting tables from connection and then join them in Power BI Desktop as required

[Anonymous]

Hi @akim1 

Please define exactly what you are after.

You wrote that - I'm trying to filter a "Company Code" column from a "Company" table where the value is "OT110".

You did not mention any joins or other tables.

It is good if you give us the full picture of your project, and not just the bottom line.

Cheers!
A

[Anonymous]

Hey @akim1 

Use DAX. It is quit simple.

Your query will look something like:

EVALUATE
FILTER ( Company, Company[Company Code] = "OT110" )

Good Luck!
A

[Anomoyes]

hi if I have filter the date is on or before end of current month, for eg this month is may so till 31 may... Which dax is helpful