Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I recently noticed that my invoke function stopped working on my personal gateway despite the fact that it works on my desktop application. The invoke function also previously worked on the gateway. However, now all results of the invoke function have thrown errors across all my different reports which use the same invoke function.
I noticed this after I made a few changes were made:
I am unsure which is responsible for the invoke function to stop working on the gateway.
Solved! Go to Solution.
I have isolated the problem and rectified the issue.
The gateway update had reset the privacy level settings for the data source to "Private" instead of "Organizational". Once this was remedied, the Invoke Function returned the intended values.
Thanks everyone for all your assistance!
I have isolated the problem and rectified the issue.
The gateway update had reset the privacy level settings for the data source to "Private" instead of "Organizational". Once this was remedied, the Invoke Function returned the intended values.
Thanks everyone for all your assistance!
Those cursed privacy levels! Good catch @Alison .
Glad you are back up and running!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@Alison , refer if these can help
https://docs.microsoft.com/en-us/power-query/handlinggatewaysupport
https://community.powerbi.com/t5/Desktop/Define-and-invoke-function-within-query/td-p/619982
https://community.powerbi.com/t5/Desktop/Invoke-function-within-Query/td-p/425706
https://www.youtube.com/watch?v=yrgEdP5T2Bw
Troubleshoot gateway
https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem-tshoot
https://guyinacube.com/2016/09/21/troubleshooting-connectivity-tips/
https://www.youtube.com/watch?v=mGnNn6gnrKk
@Alison - Sorry, Invoke, is that a DAX function or ??
Hi @Greg_Deckler,
Invoke Function is added in Power Query. It converts UTC to AEST detailed in this post: https://community.powerbi.com/t5/Desktop/UTC-to-AEST/m-p/188878/highlight/true#M83075
It used to return a value on the gateway and still does on the desktop application. However it now returns a blank value only on the gateway which is really strange.
Cheers
@Alison - Oh, so it is just a custom M function? Can you paste the code from Advanced Editor for it?
@Greg_Deckler, the invoke function advanced editor code is below:
Source = (DateTimeUTC as datetimezone) as datetimezone => try DateTimeZone.SwitchZone(DateTimeUTC,Table.Last(Table.SelectRows(UTC_OffSet, each [UTC] <= DateTimeUTC))[UTCOffset]) otherwise null
Where the UTC_Offset is a table with 3 columns:
AEST = datetime when the daylight savings turns on and off in AEST
UTCOffset = either 10 or 11 depending on the UTC + hours depending on daylight savings
UTC = datetime when the daylight savings turns on and off in UTC
@Alison - I don't see anything particularly offensive. @ImkeF @edhans ?
I don't see anything obvious @Alison as to why that would stop working. Have you upgraded to the latest personal gateway? If your Desktop app is on a newer version than your gateway, you may just need to get them in sync. Your local PBIX file may be upgraded in a way an older gateway doesn't understand.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans,
The invoke function only stopped working after I updated my gateway to August 2020. My desktop application is also August 2020.
Can you be clearer on what you are seeing? You've said it throws errors, and that it returns blanks. Errors and blanks are not the same thing. And @ImkeF made a valid point - your try/otherwise construct should eliminate true errors barring a bug in the program.
Sometimes to aid in troubleshooting this, create a simple table that uses your custom function, then load that into the model and just create a table visual with all of those fields to see what it is doing.
If it is an error, what is the error returned by the service?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @Alison ,
how do you know that it's that function that causes the error?
You've included an error-handling statement there, so it should run return null if somethings getting wrong.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ImkeF ,
So when I run the function on my desktop the Invoke induction produces the intended results into a new column as expected. However after I publish the pbix file, and refresh it over the gateway, I noticed the new column where I should have my intended results are null.
I broke down each step and republished each time to troubleshoot what the gateway was struggling with:
This is how I concluded it was the Invoke Function struggling. However, it did work with no issues before I did the following three things:
Imke might have an idea, but at this point, I'd be inclined to open a support ticket @Alison - your above steps are pretty good trouble shooting steps that will assist them. You can create a ticket at the bottom of this page.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
64 | |
27 | |
25 | |
17 | |
11 |