cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
katecoriell Frequent Visitor
Frequent Visitor

Schedule Refresh Error due to Function used in Desktop

Hi PBI gurus!

 

Greatly appreciate any help or guidance on this issue. We are using a Function (fGetDuration) from another table in our data model. All tables come from the same sql database (Practice Engine). Everything works just fine in Desktop, including data refresh. No problem pushing up to the Service either. (And before putting in this function, scheduled refresh worked in the Service as well.)

 

Background info: this is a pipeline report that is used at the "opportunities pipeline" meeting by the partners. This meeting typically occures every 14 days, but sometimes pushed back n days. The "current" period is listed as current in Dynamics 365 with dates for the meeting following under it. The partners want to see what new opportunities or win/loss numbers in the current period at the pipeline meeting (it does have a start date but all of this is kinda messy and I have to work within this). We want to code these opportunities as green if the created date is within the current period, or yellow if modified date is within the current period. We are wanting to make sure this is dynamic and updates with the daily scheduled refresh of the pipeline PBI report in the service.

 

The issue comes when we try to do a scheduled refresh in Service with the updated conditional column that has the fGetDuration function. I have included a screenshot of the code in Query Editor, and included below because the screenshot cuts off the last bit. Also included is a screenshot of the function in Query editor.

 

Screen Shot 2019-01-22 at 10.55.58 AM.png

 

Screen Shot 2019-01-22 at 10.58.23 AM.png

 

Greatly appreciate any help! Or another way to accomplishing this dynamic need.

Thanks so much!

katie

 

#"Renamed Columns11" = Table.RenameColumns(#"Extracted Days",{{"Age.1", "ModifiedAge.1"}}),
#"Row Coloring" = Table.AddColumn(#"Renamed Columns11", "Row Color", each if [Days] < fGetDuration() then "green" else if [Days] <= 180 then "white" else if [ModifiedAge.1] < fGetDuration() then "yellow" else "red"),
#"Row Hex Coloring" = Table.AddColumn(#"Row Coloring", "_*Row Hex Color", each if [Row Color] = "green" then "#90EE90" else if [Row Color] = "white" then "#FFFFFF" else if [Row Color] = "yellow" then "#FFDB44" else "#FA8072")

5 REPLIES 5
katecoriell Frequent Visitor
Frequent Visitor

Schedule refresh ERROR in Service from Function fGetDuration created in Desktop

Hi everyone,

 

Greatly appreciate any help or guidance on this issue. We are using a Function (fGetDuration) from another table in our data model. All tables come from the same sql database (Practice Engine). Everything works just fine in Desktop, including data refresh. No problem pushing up to the Service either. (And before putting in this function, scheduled refresh worked in the Service as well for same tables from the same database.)

 

We are using a function to get table columns to code to a specfic color based on being in the "current" pipeline period. We want opportunities that have been created in the current period to be green, modified in the current period to be yellow, and red if modified 180+ days ago. The "current" period is typically 14 days but can be different due to when the pipeline meeting is held (this is updated/adjusted from Dynamics 365 / CRM).

 

The issue comes when we try to do a scheduled refresh in Service with the updated conditional column that has the fGetDuration function. I have included a screenshot of the code in Query Editor, and included below because the screenshot cuts off the last bit. The second photo is of the created Function (based on a different table from same database) in PBI Desktop that is then inserted into the code for another table/created column.

 

Screen Shot 2019-01-22 at 10.55.58 AM.png

 

 

 

Screen Shot 2019-01-22 at 10.58.23 AM.png

 

 

 

 

 

 

 

 

 

Any help is greatly appreciated! If there is a different idea to get this dynamic color coding to work, would greatly appreciate that too. 

 

Thank you!

Katie

 

 

#"Renamed Columns11" = Table.RenameColumns(#"Extracted Days",{{"Age.1", "ModifiedAge.1"}}),
#"Row Coloring" = Table.AddColumn(#"Renamed Columns11", "Row Color", each if [Days] < fGetDuration() then "green" else if [Days] <= 180 then "white" else if [ModifiedAge.1] < fGetDuration() then "yellow" else "red"),
#"Row Hex Coloring" = Table.AddColumn(#"Row Coloring", "_*Row Hex Color", each if [Row Color] = "green" then "#90EE90" else if [Row Color] = "white" then "#FFFFFF" else if [Row Color] = "yellow" then "#FFDB44" else "#FA8072")

Community Support Team
Community Support Team

Re: Schedule Refresh Error due to Function used in Desktop

Hi @katecoriell

Could you show an screenshot of the refresh error message?

 

Best Regards

Maggie

katecoriell Frequent Visitor
Frequent Visitor

Re: Schedule Refresh Error due to Function used in Desktop

Screen Shot 2019-01-23 at 8.15.35 PM.png

 

And here is the code from the table: factOpportunitiesLeaders

 

let
Source = factOpportunities,
#"Removed Other Columns" = Table.SelectColumns(Source,{"_new_opportunityleader1id_value", "_new_opportunityleader2id_value", "_new_opportunityleader3id_value", "_new_opportunityleader4id_value", "opportunityid"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"opportunityid", "_new_opportunityleader1id_value", "_new_opportunityleader2id_value", "_new_opportunityleader3id_value", "_new_opportunityleader4id_value"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"opportunityid"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Leader"}})
in
#"Renamed Columns"

katecoriell Frequent Visitor
Frequent Visitor

Re: Schedule Refresh Error due to Function used in Desktop

Hi Maggie @v-juanli-msft

 

Just wanted to check to see if you have had a chance to look into this issue. Let me know if you need more information than what I provided with the code above. 

 

Thanks so much!

Katie

Highlighted
Community Support Team
Community Support Team

Re: Schedule Refresh Error due to Function used in Desktop

Hi @katecoriell

I can't reproduce this issue as you did.

If you have a pro license, i suggest you create a support ticket and trace power bi with fildder.

 

Best Regards

Maggie