Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
akim1
Regular Visitor

Pre-Filtering Data Before Importing

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

 

 

8 REPLIES 8
grazitti_sapna
Resolver I
Resolver I

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
Regular Visitor

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

@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
Regular Visitor

@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. 

@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
Not applicable

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
Not applicable

Hey @akim1 

Use DAX. It is quit simple.

Your query will look something like:

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

Good Luck!
A

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.