cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Cutting out unnecessary data from report

HI All,

 

A very simple on but a little unsure how to tackle this.

 

I have a report that has around 6 tables all related. I am only interested in data after the 1st November, so the 5/6 years od data prior is not needed. Now I can filter the report etc, but is there a way stop queries looking at this dtaa in the first place. I know how to do this on a per table basis but it stll look at the all reocrds for other tables.

 

Hope that makes sense.

 

Thanks

 

Craig

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft v-huizhn-msft
Microsoft

Re: Cutting out unnecessary data from report

Hi @CraigBlackman

You your resource data in SQL Server database, right? If it is, you can use Query to filter your date when you import table. Please program the SQL statement in the following windows.

Capture1.PNG

For example, I have table tables, Date_Table and Table_1, they are both have date columns.

When you import the Date_Table, please use the formula below query. It will return all the date after 2016/11/1.

 

select * from Date_Table T1 where T1.Date>='2016-011-01'


When you import the Table_1, please use the formula below query. It will display all the data in Table_1 except related data after 2016/11/1.

Select * from Table_1 T2 inner join Date_table T1 on T1.Date=T2.Date where T1.Date>='2016-11-1'



 

You can use the similar Query to import other four tables. You will stop query the old data’s records.


Best Regards,
Angelia

View solution in original post

2 REPLIES 2
Highlighted
Microsoft v-huizhn-msft
Microsoft

Re: Cutting out unnecessary data from report

Hi @CraigBlackman

You your resource data in SQL Server database, right? If it is, you can use Query to filter your date when you import table. Please program the SQL statement in the following windows.

Capture1.PNG

For example, I have table tables, Date_Table and Table_1, they are both have date columns.

When you import the Date_Table, please use the formula below query. It will return all the date after 2016/11/1.

 

select * from Date_Table T1 where T1.Date>='2016-011-01'


When you import the Table_1, please use the formula below query. It will display all the data in Table_1 except related data after 2016/11/1.

Select * from Table_1 T2 inner join Date_table T1 on T1.Date=T2.Date where T1.Date>='2016-11-1'



 

You can use the similar Query to import other four tables. You will stop query the old data’s records.


Best Regards,
Angelia

View solution in original post

Re: Cutting out unnecessary data from report

Thanks you for the response, completely missed the obvious solution.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors