Power Query Values not Updating Values when Report is Deployed to Power BI Report Server

[Anonymous]

Hi all,

(Last post got deleted automatically when I changed the title!)

I have read a lot of posts around this but couldn't come to an exact conclusion so thought I would post, apologies if I have missed the answer somewhere else.  This is my first time with a direct query report, as I would usually use import myself.

-

I have a Direct Query report.  Within one of the tables is some DAX code (extract from Advanced Editor):

// Calculate the end date of next month
CurrentDate = DateTime.Date(DateTime.LocalNow()), // Get current date
CurrentDateMonthStart = Date.StartOfMonth(CurrentDate), // Get first date of current month
NextMonthStartDate = Date.AddMonths(CurrentDateMonthStart,1), // Get next month start date
NextMonthEndDate = Date.EndOfMonth(NextMonthStartDate), // Get next month end date
NextMonthEndDateId = Number.FromText(Date.ToText(NextMonthEndDate,"yyyyMMdd")), // Convert to Number

// THEN
// trim down the date table and only collect data that is <= Next Month End Date
DateRange = Table.SelectRows(dbo_dim_date,each [dim_date_key]<=NextMonthEndDateId)

As you can see, various dates are being set, and then the data in the table is being filtered (using "SelectRows") so only data up to the end of the following month is there.

When I refresh this within Power BI Desktop application it works flawlessly.

However, the version that is deployed to our Power BI Server is currently stuck at the end of February.  I am presuming that is because none of these values are updating?  (The report was last updated in January - so "End of Next Month" would be Feb.)

So my question is - why aren't these values being updated at all?

Do I need to set some form of DAX Value  Refresh to update the values each day?  I couldn't see any options for a refresh on this Direct Query report.

Or is this possibly a limitation of Direct Query, in the fact that these DAX Values cannot be updated without opening and refreshing the report within the Desktop Application? 

Thanks in advance for any answers!

[Anonymous · Accepted solution]

@Anonymous 

I am sorry that I am confused with your post. To clarify something:

1. In the first place, are you sure you are using Power BI Report Server not Power BI Service? There 2 different products.

2. Your code is M (power query) not DAX functions(power bi desktop report layer).

3. Refresh in power bi report server is refreshing the new updated datasource data not the new query code in the pbix file, change the query will not make change to the report on report server unless you upload the new pbix to Report Server, this is same as report in Power Bi Service.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

Paul Zheng _ Community Support Team

[Anonymous]

Hi @Anonymous ,

Sorry for the confusion - I had updated the title to Power Query but forgot to change the detail within the ticket.

We are using Report Server.

---

@Anonymous wrote:

Refresh in power bi report server is refreshing the new updated datasource data not the new query code in the pbix file, change the query will not make change to the report on report server unless you upload the new pbix to Report Server, this is same as report in Power Bi Service.

---

That confirms what I thought, the Power Query values are not being updated unless the report is refreshed locally and then re-uploaded. 

The report was given to me after the report creator left, and I don't want to have to re-upload it every month, so I will research a better way to do it.

Thank you! 🙂 

[josef78]

I'm not sure about question, but sure Power Query values are updated during Refresh data, no matter if you run refresh in Power BI Desktop or Power BI Report Server or Power BI Service. Only in case when "Include in report refresh" is off, then source is not updated.