cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Really bizarre issue with SQL function not returning result in Desktop but does on service!!!!

I had a model that was working perfectly well and then our servers moved to Azure. this caused some issues with some custom SQL functions that calculated duration.  These were fixed  and if I run the query below in Microsoft Server Management it works fine generating the DurationSecInWorkinghours column

 

select
   'TEST' AS [Client],
   noteid,
   [Type],
 
   CaseNumber,
   CreatedDateTime,
   FinalisedDateTime,
   FinalisedByAdviserID,
   t.TopicTitle,
   [ARGOS_DW_REPORTING].[dbo].[udf_GetWorkingTime](CreatedDateTime, FinalisedDateTime) AS DurationSecInWorkingHours
   from [ARGOS_DW_REPORTING].[dbo].[Fact_Cases_Notes] fcn
   JOIN [ARGOS_DW_REPORTING].dbo.Dim_Topic t    on t.TopicKey = fcn.TopicKey
   where FinalisedDateTime >= '20200810' 
   and FinalisedByAdviserID <> - 1
   and PostedToCaseViewerFlag <> 'T'
   AND COALESCE(AskTheExpertID, 0) = 0

 

However when I put this into aquery in Power BI it produces zeros for some but not all clients (we have seperate DB for each client).

 

let
    Source = Sql.Database("Test.com", "master", [Query="
  select
   'TEST' AS [Client],
   noteid,
   [Type],
 
   CaseNumber,
   CreatedDateTime,
   FinalisedDateTime,
   FinalisedByAdviserID,
   t.TopicTitle,
   [ARGOS_DW_REPORTING].[dbo].[udf_GetWorkingTime](CreatedDateTime, FinalisedDateTime) AS DurationSecInWorkingHours
   from [ARGOS_DW_REPORTING].[dbo].[Fact_Cases_Notes] fcn
   JOIN [ARGOS_DW_REPORTING].dbo.Dim_Topic t    on t.TopicKey = fcn.TopicKey
   where FinalisedDateTime >= '20200810' 
   and FinalisedByAdviserID <> - 1
   and PostedToCaseViewerFlag <> 'T'
   AND COALESCE(AskTheExpertID, 0) = 0


    "])
in
    Source

 

On the TEST DB I have 5 clients so code above just joined with UNION ALL.  4 of them have results in desktop, but one doesn't. However if I publsih it out all 5 are OK!!!  We have second server TEST2 and on this one there are 12 clients and none of them have anything but zeros and none of them work when published (the query works in MSMS). 

 

The M code is identical to the code on the pre-Azure servers just the server name changed.   So questions

 

  1. Any ideas what would cause the query to return the results in MSMS but not in desktop. I am using the same windows login?
  2. What would cause the desktop model to have a zero result, but for the result to be correct when refreshed on the service (for that one client that does this)?

    One issue could be that each client has a differnet definition of working hours so each function is a bit different.  Even so I'm baffled why MsMS can run it and desktop cant and also why the serve can run it and desktop cant.  We are out of ideas and compeltely crippled our Power BI

 

Thanks for any advice

 

Mike

 

2 REPLIES 2
Highlighted
Super User V
Super User V

Re: Really bizarre issue with SQL function not returning result in Desktop but does on service!!!!

Hi @masplin ,

 

Perfect person to answer this questions is @ImkeF the best super user on M language here.

 

@ImkeF  can you sort this thing out?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Super User IV
Super User IV

Re: Really bizarre issue with SQL function not returning result in Desktop but does on service!!!!

Hi @masplin ,

nothing super obvious comes to my mind here. Just that the SQL-code looks a bit rough in general.

I would start rebuilding the SQL-query with native Power Query steps, check the auto-generated M-Code (view native query) and replace your SQL-code with it subsequently.

Do this step by step until PBID hits the error. That should identify the delinquent query element(s).

 

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

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors