Need Opinion Regarding Parameter

[user023]

I need opinion on better method to do this. 

I have transactional data, which is very large stored in SQL. So we set only store for laterst 30days.

Then I load the data into SQL and do some calculations using DAX. (not import only use stored procedure)

The report consist of multiple dynamic filtration (eg startdate, enddate, process name etc) which using DAX no issue to show the calculations. 

However, user request to have all time data inside of report which I dont think I will  load the whole transaction even into SQL nor Power BI. 

So here I need to think another approach. 

I need to re-do calculations inside the SQL so pass only calculated field however when doing this dynamic filtration will not works. 

Example: 

Data from SQL

Calculation in Power BI

Based on selected date and process able to take how many total machine and how many days required etc.

If calculation in SQL 

All need to calculated by day, by process. 

But then when I pass to power BI if user filter multiple date need to recount with another set of formula which I feel redundant to be done. 

So my question what to do if I want

1. To have my data available all the time? Any feature able to save from power BI data to SQL? 

2. If not what approach should I take? 

[v-xinruzhu-msft · Accepted solution]

Hi @user023 

If you want to  have your data available all the time, you can consider to change your data mode to direct query, it allows you to make the change to the data source without import your data to Power BI again, the direct query is connected to the data source directly, it will put the change to Power BI automatically , you can refer to the following link.

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about#directquery-use-ca...

What Is DirectQuery Mode in Power BI? - ONLC

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.