Power BI dataset not updating with DAX function

[mlin3]

Hi All

I have an excel workbook in TEAMS and i connect it to Power BI through sharepoint weblink. In the workbook, I am useing a DAX function =IF([@Date]<TODAY(),0,"") to update the value "0" daily. I realised that the dataset will not update when i refresh, even though my data in TEAMS is updated. I have to physically enter "0" in any of the field and all the records for the past few days will be updated accordingly. Any idea what is happening?

Regards

Dylan

[Jimmy801 · Accepted solution]

Hello @mlin3 

okay... this is not DAX, this is just a simple Excel-formula.

Power BI is reading the current stored data, so with the data when you saved the workbook the last time.

This means the formula are not recalculated when you refresh Power BI. There are two solutions

A) Before refreshing Power BI you have to open the workbook as well

B) Read data without formulas (at least that are calculated dynmically like your date-formula) from Excel and reproduce the formulas in Power BI

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Icey · Accepted solution]

Hi @mlin3 ,

Just like what @Jimmy801 mentioned, you need to create a calculated column in Power BI Desktop, not in the Excel workbook.

Reference: Tutorial: Create calculated columns in Power BI Desktop

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[Jimmy801]

Hello @mlin3 

I cannot follow you. I don't know how your Excel is structured, but if you have data in your Excel-sheet that is then transformed to Power Pivot to add a DAX function, you need also to export this data again to your Excel-sheet to read it with Power BI. So, when you are talking about "after refresh", what exactly you mean. The data refresh of you Excel-data before handing over to Power Pivot? Can you explain me a little bit better.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[mlin3]

Hi Jimmy

Below is the excel workbook I have in TEAMS, there is no pivot table involve. The rows will automactically be updated with value "0" daily (refer to the DAX function). However, when i do a refresh manually in Power BI Desktop/Power BI Service, the data was not updated accordingly. The data will be updated and reflected in power BI ONLY IF i enter into TEAMS to modify the data (i.e. Key in value manually and replaced the DAX function). The reason i use the DAX function is to save the trouble to update the data since most of the days the value will be "0", and with the scheduled refresh, everything should work perfectly. Hope this provide a clearer picture to you.

Excel Workbook in TEAMS

[Jimmy801 · Accepted solution]

Hello @mlin3 

okay... this is not DAX, this is just a simple Excel-formula.

Power BI is reading the current stored data, so with the data when you saved the workbook the last time.

This means the formula are not recalculated when you refresh Power BI. There are two solutions

A) Before refreshing Power BI you have to open the workbook as well

B) Read data without formulas (at least that are calculated dynmically like your date-formula) from Excel and reproduce the formulas in Power BI

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy