Using parameter in SQL server and map value of this parameter to filter control in report

[hellgoingon]

Hi all,

I'm having a big data in AA table which i cannot run query "select * from AA" because it will cost me 2 hours to run query.

So i've just want to display data defaultly from July 1st to July 30th to prevent loading much data.

But when going to report (display charts related to that data), i can filter data base on filter control and maybe select data from Apr 1st to apr 30th

How can i do that?

[hellgoingon]

Any helps please?

[v-chuncz-msft]

@hellgoingon,

You may create a parameter and then reference it in Advanced Editor.

https://community.powerbi.com/t5/Developer/Auto-Update-URL-Data-Source/m-p/56893#M1802

[hellgoingon]

Thanks. There are anyways to access value of controls (on report) in Advand editor? 

[v-chuncz-msft]

@hellgoingon,

There is no other way.

[hellgoingon]

So how can i solve below problem: I have data from 2008 -> now (2017). Data of each years are round 100,00,000 records. So 9 years = nearly 1 billion records.

Time of loading data (2008 -> 2017) are over 2 hours. Absolutely i cannot load all data each time accessing report page. so i load data in only current year (2017) to speedup loading.

But the problem is if loading data in 2017, user cannot filter data in 2008 -> 2016 whatever they do on report page.

So how can i do: loading data in only current year (2017) but user can see data of 2008 -> 2016 via filter on report page?

[v-chuncz-msft]

@hellgoingon,

You may try DirectQuery mode.

[hellgoingon]

Yup! i already use DirectQuery but it query all data when loading report -> That makes speed very slow. After query all data, it will display data base on filter controls on reports

[v-chuncz-msft]

@hellgoingon,

Check if the following link helps.

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-directquery-about/#guidance-for-us...