Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |