Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mlin3
Regular Visitor

Power BI dataset not updating with DAX function

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

2 ACCEPTED SOLUTIONS
Jimmy801
Community Champion
Community Champion

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

View solution in original post

Icey
Community Support
Community Support

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.

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

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
Community Champion
Community Champion

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

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 TEAMSExcel Workbook in TEAMS3.JPG

Jimmy801
Community Champion
Community Champion

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors